Help to create macro

[VegasNath]

Hi,

I am trying to create a macro, that I would be grateful for some assistance with, as to be honest, I do not know where to start.

Please see the attached dummy file representing what I am attempting to achieve. In the real world, I will be working with an excel 2007 file that is over 100,000 rows long.

What I need to do is find the row number on the report tab where matching data is found in columns A, D & F.

I would appreciate any help that can be offered. Thanks

[HansV]

Enter the following array formula im J2 (confirm with Ctrl+Shift+Enter):

=MATCH(A2&D2&F2,Report!$A:$A&Report!$D:$D&Report!$F:$F,0)

and fill down. It will probably be very slow if the report sheet has 100,000 rows - this might be better suited to an Access database.

[VegasNath]

Hans, Thankyou very much, that works perfectly!

The report is actually just over 400,000 rows of data, but the array formula is pretty fast, I can certainly live with that. You are most definately correct in that this is probably better suited to access, however that is not a supported application here so I have to live with Excel 2007, which I am slowly (very slowly) getting to grips with.

Anyhow, next question. Using your array formula solution, I am able to ascertain the start and end rows of the data that I require from a daily 400k row report, see attached. How can I now use the green cells to create an array of rows to "not delete" and delete the rest.

In the example, I would want to delete like so:

Range("1:74333, 74429:91059, 91126:223691, ........ ETC").Delete Shift:=xlUp

****** The data provided represents actual excel 2007 row data generated by the array formula, and is set up as in the 2007 workbook, but does not represent the "report" tab.

Thanks again for your assistance.

[HansV]

The string would probably become too long. It's better to loop backwards and delete each individual range, as suggested by Jan Karel Pieterse:

Code: Select all

Sub DeleteRows()
  Dim wshL As Worksheet
  Dim wshR As Worksheet
  Dim lngRow As Long
  Dim lngMaxRow As Long
  Dim lngStart As Long
  Dim lngEnd As Long
  Set wshL = Worksheets("LookUpRowNumber")
  Set wshR = Worksheets("Report")
  lngMaxRow = wshL.Range("O" & wshL.Rows.Count).End(xlUp).Row
  For lngRow = lngMaxRow To 2 Step -1
    If lngRow = 2 Then
      lngStart = 1
    Else
      lngStart = wshL.Range("P" & (lngRow - 1)) + 1
    End If
    lngEnd = wshL.Range("O" & lngRow) - 1
    wshR.Range(lngStart & ":" & lngEnd).Delete
  Next lngRow
End Sub

[VegasNath]

Thanks Hans.

I need to take a step back. I altered the array formula from:

=MATCH(A2&D2&F2,Report!$A:$A&Report!$D:$D&Report!$F:$F,0)

to:

=IF(ISERROR(MATCH(A2&D2&F2,'Report'!$A:$A&'Report'!$D:$D&'Report'!$F:$F,0))=TRUE,0,MATCH(A2&D2&F2,'Report'!$A:$A&'Report'!$D:$D&'Report'!$F:$F,0))

....because "Report" gets imported from a text file. However, when "Report" is imported, the array formula changes to:

=IF(ISERROR(MATCH(A3&D3&F3,#REF!&#REF!&#REF!,0))=TRUE,0,MATCH(A3&D3&F3,#REF!&#REF!&#REF!,0))

How can I get around this?

[VegasNath]

Correction, when I delete the Report, the formula corrupts.

How can I enter the array formula into the cells after the import using vba? Or if possible, the value result of the formula?

[HansV]

Try code like this:

Code: Select all

  Worksheets("LookUpRowNumber").Range("J2").FormulaArray = _
    "=MATCH(A2&D2&F2,Report!$A:$A&Report!$D:$D&Report!$F:$F,0)"
  With Worksheets("LookUpRowNumber").Range("J2:J28")
    .FillDown
    .Value = .Value
  End With

Adjust the range as needed.

[VegasNath]

Thanks Hans, I am just about there. The data deletion needs to start from the end of the data (last row) upto (in the example) row 396705, which it is not currently doing? How can I incorporate this?

[HansV]

Does this do what you want?

Code: Select all

Sub DeleteRows()
  Dim wshL As Worksheet
  Dim wshR As Worksheet
  Dim lngRow As Long
  Dim lngMaxRow As Long
  Dim lngStart As Long
  Dim lngEnd As Long
  Set wshL = Worksheets("LookUpRowNumber")
  Set wshR = Worksheets("Report")
  lngMaxRow = wshL.Range("O" & wshL.Rows.Count).End(xlUp).Row
  For lngRow = lngMaxRow + 1 To 2 Step -1
    If lngRow = 2 Then
      lngStart = 1
    Else
      lngStart = wshL.Range("P" & (lngRow - 1)) + 1
    End If
    If lngRow = lngMaxRow + 1 Then
      lngEnd = wshR.Range("A" & wshR.Rows.Count).End(xlUp).Row
    Else
      lngEnd = wshL.Range("O" & lngRow) - 1
    End If
    wshR.Range(lngStart & ":" & lngEnd).Delete
  Next lngRow
End Sub

[VegasNath]

I'll check next week and let you know. Thanks Hans.