Home/ Questions/Q 2836
Next
In Process
Computing Staff
  • 0
Asked: 2021-10-29T08:20:00+00:00 In:

Delete Rows Between Two Lines Using A Macro

  • 0

I am trying to format a report that was originally saved in .txt format. I’m having to perform this duty quite frequently so I’ve been trying to create a macro to assist but am having difficulty.

These reports can be from 3 to 1,500 pages long. Where I’m having the most difficulty is trying to find a way to delete all rows between the rows that begin with “END OF CSA” and “RUN DATE” throughout the entire report. The number of rows that need to be deleted could vary for every instance where these two sets of data are found. Is there a way to accomplish this?

Share

    1 Answer

    1. This code should work, but I suggest you try it in a backup copy of your file in case things go terribly wrong. Obviously I can only test it in a workbook that I think looks like yours since I can’t see your workbook from where I’m sitting.

      Note: if you read this How To, you’ll find some troubleshooting tips that might help you determine what is or isn’t working with the code.

      http://www.computing.net/howtos/sho…

      If it doesn’t do what you want, please post an example of your data, after clicking on the blue line at the end of this post and reading the instructions found via that link.

      Sub DeleteRows()
'Determine Last Row with data in Column A
 lastRw = Range("A" & Rows.Count).End(xlUp).Row
'Loop through Rows in reverse order
  For nxtRw = lastRw To 1 Step -1
'If cell contains RUN DATE, save the Row number from the cell above
   If Range("A" & nxtRw) Like "*RUN DATE*" Then _
    runRw = Range("A" & nxtRw).Row - 1
'If cell contains END OF CSA, save the Row number from the cell below
'and use the Row number variables to delete the Rows between the 
'2 text values
   If Range("A" & nxtRw) Like "*END OF CSA*" Then
     csaRw = Range("A" & nxtRw).Row + 1
     Rows(runRw & ":" & csaRw).Delete shift:=xlUp
   End If
  Next
End Sub

      Click Here Before Posting Data or VBA Code —> How To Post Data or Code.

      • 0