Solved Delete rows between two lines using a macro

January 23, 2013 at 07:32:48
Specs: Windows 7
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?


See More: Delete rows between two lines using a macro

Report •


✔ Best Answer
January 24, 2013 at 08:01:40
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.



#1
January 23, 2013 at 07:49:21
Is the data in an Excel spreadsheet or is it in a text file?

If it's in a spreadsheet, we could provide some VBA code to perform the task, but if it's in a text file, well, I wouldn't be able to help, but maybe someone else could.

If it's in a spreadsheet, I have 2 questions:

1 - Where would "END OF CSA" and "RUN DATE" be found, i.e. in what column?

2 - You said "delete all rows between the rows that begin with "END OF CSA" and "RUN DATE". Do you also want those 2 rows deleted?

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


Report •

#2
January 23, 2013 at 08:17:25
It was in .txt format, but I simply changed the file extension to .xls and opened the spreadsheet.

All data is in Column A. Due to the way the data is on the report, it impossible to separate the data into columns without losing or distorting the data.

Regarding your second question, I do need to keep the "END OF CSA" and "RUN DATE" rows.

Thank you so much for any help you can provide!!


Report •

#3
January 23, 2013 at 08:22:40
I hope I'm clear in what I'm attempting to do, I want to delete every row after "END OF CSA" and before "RUN DATE" for every instance throughout the report.

Report •

Related Solutions

#4
January 24, 2013 at 08:01:40
✔ Best Answer
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.


Report •

#5
January 24, 2013 at 09:38:07
Ok DerbyDad03, I ask for your patience...lol!

Creating a macro in excel and then using the Alt+F8 function to select the macro are really the only things I know about working with them. I have no clue how to edit ones that I've created or how to delete them.

So, I'm not sure what I'm supposed to do with this code. Can you provide the instructions on where I should add it? I'm okay with it being a separate macro from the other one I've created for the report, but I'm open to doing whatever you advise.

I'm so grateful for your help!!!!!


Report •


Ask Question