Macro to Delete Rows above certain word in column

[dariushou]

Hi there, I'm having a little problem figuring this one out. I have several spreadsheets of cash flows with each set of cash flows starting out in different rows. What i would like to do is delete all of the rows above the cash flows. The cash flows always start below the word total which is always in column A. So if i could create a macro that says delete all rows above the word total when total is found in column A it would work.

Also, within the multiple spreadsheets are multiple worksheets. I would like the macro to automatically go in a folder and open all of the workbooks and delete the rows above the word total for each of the worksheets (the cash flows on each worksheet within the workbook also start on different rows).

Thanks for your time.

Darius

 

[Mike Blackman]

Hi Darius,

Maybe something like this;

Sub Find_CashFlows()

x = Columns("A").Find("Total").Row

Rows("1:" & x - 1).Delete

End Sub

 

[Mike Blackman]

For multiple Sheets;

Sub Find_CashFlows()

For i = 1 To Sheets.Count

With Sheets(i)
    x = .Columns("A").Find("Total").Row
    .Rows("1:" & x - 1).Delete
End With

Next i

End Sub

 

[dariushou]

Thanks Schwarzmanne. That worked for one workbook--however i to get a run-time error 91 after the macro is finished. The macro finishes, but then i get an error. Not sure what's that about. Did you know how to open every file within a folder and have the macro process all of the files.

Thanks!!!

 

[Mike Blackman]

Hi Darius,

Not sure whats causing the error to be honest, does the final sheet have a cell "Totals" like the other sheets? Maybe its erroring due to not finding a match, I'm unsure,

As for All workbooks in a folder, I'm afraid thats a bit beyond me for the moment, I'm sure one of the Guru's on the board can suggest something for that though.

 

[dariushou]

Thanks for your help Schwarzmanne!!

Does anyone know how i can go into say 15 files and use Schwarzmanne's macro to delete the rows above the word "total" in column A.

In fact, what i really want to do is have the macro look at the FIRST sheet in all 15 files and delete all of the rows above "Period" when it occurs in column A. I then would like it to look at the rest of the sheets in the workbook and do the same thing, but this time the word in column A is "Total".

Not sure if i have to open the files with the macro and then delete the rows, save, and close.

Any ideas on how to do all of this?

Thanks,
Darius