Help with deletion of Data in Multiple Sheets
 

I have about 50 sheets in a Workbook. Every time I have to manually delete
certain data from all sheets. I want to delete all data below "Total Number
of Records"

E.g
Total Number of REcords
My Name is PP
Your name is LL
Today's Date..
Total No of Days

Please note that there could be more than four rows of data below "Total
Number of Record". Preferably it should look through all rows e.g.(A20) of
data below the title and delete all. If there is a break space in the rows it
should stop (Cos some data might be below the empty row) and they do not need
to be deleted.

Can you please help me... All help totally appreciated.

Thanks a Great deal

Help with deletion of Data in Multiple Sheets
 

Hi

I loop through column A and delete entire rows below "Total Number of
Records" and down to first empty row. Continue with next sheet.

Try this:

Sub DeleteData()
TargetCol = "A"
For Each sh In ThisWorkbook.Sheets
If ActiveSheet.Name < sh.Name Then
Sheets(sh.Name).Select
End If
Set f = Columns(TargetCol).Find(What:="Total Number of Records")
fRow = f.Row
LastRowToDelete = Cells(fRow, TargetCol).End(xlDown).Row
Range(Rows(fRow + 1), Rows(LastRowToDelete)).Delete
Next
End Sub

Regards,
Per

On 24 Okt., 04:15, Yossy wrote:
I have about 50 sheets in a Workbook. Every time I have to manually delete
certain data from all sheets. I want to delete all data below "Total Number
of Records"

E.g
Total Number of REcords
My Name is PP
Your name is LL
Today's Date..
Total No of Days

Please note that there could be more than four rows of data below "Total
Number of Record". Preferably it should look through all rows e.g.(A20) of
data below the title and delete all. If there is a break space in the rows it
should stop (Cos some data might be below the empty row) and they do not need
to be deleted.

Can you please help me... All help totally appreciated.

Thanks a Great deal

Help with deletion of Data in Multiple Sheets
 

please i get error "Compilation code" and it highlighted the " Sub
DeleteData()".

Thanks for helping.

"Per Jessen" wrote:

Hi

I loop through column A and delete entire rows below "Total Number of
Records" and down to first empty row. Continue with next sheet.

Try this:

Sub DeleteData()
TargetCol = "A"
For Each sh In ThisWorkbook.Sheets
If ActiveSheet.Name < sh.Name Then
Sheets(sh.Name).Select
End If
Set f = Columns(TargetCol).Find(What:="Total Number of Records")
fRow = f.Row
LastRowToDelete = Cells(fRow, TargetCol).End(xlDown).Row
Range(Rows(fRow + 1), Rows(LastRowToDelete)).Delete
Next
End Sub

Regards,
Per

On 24 Okt., 04:15, Yossy wrote:
I have about 50 sheets in a Workbook. Every time I have to manually delete
certain data from all sheets. I want to delete all data below "Total Number
of Records"

E.g
Total Number of REcords
My Name is PP
Your name is LL
Today's Date..
Total No of Days

Please note that there could be more than four rows of data below "Total
Number of Record". Preferably it should look through all rows e.g.(A20) of
data below the title and delete all. If there is a break space in the rows it
should stop (Cos some data might be below the empty row) and they do not need
to be deleted.

Can you please help me... All help totally appreciated.

Thanks a Great deal

Help with deletion of Data in Multiple Sheets
 

Just a guess, did you copied the entire code including the "End Sub"
line.

Regards,
Per

On 24 Okt., 15:39, Yossy wrote:
please i get error "Compilation code" and it highlighted the " Sub
DeleteData()".

Thanks for helping.



"Per Jessen" wrote:
Hi

I loop through column A and delete entire rows below "Total Number of
Records" and down to first empty row. Continue with next sheet.

Try this:

Sub DeleteData()
TargetCol = "A"
For Each sh In ThisWorkbook.Sheets
* * If ActiveSheet.Name < sh.Name Then
* * * * Sheets(sh.Name).Select
* * End If
* * Set f = Columns(TargetCol).Find(What:="Total Number of Records")
* * fRow = f.Row
* * LastRowToDelete = Cells(fRow, TargetCol).End(xlDown).Row
* * Range(Rows(fRow + 1), Rows(LastRowToDelete)).Delete
Next
End Sub

Regards,
Per

On 24 Okt., 04:15, Yossy wrote:
I have about 50 sheets in a Workbook. Every time I have to manually delete
certain data from all sheets. I want to delete all data below "Total Number
of Records"

E.g
Total Number of REcords
My Name is PP
Your name is LL
Today's Date..
Total No of Days

Please note that there could be more than four rows of data below "Total
Number of Record". Preferably it should look through all rows e.g.(A20) of
data below the title and delete all. If there is a break space in the rows it
should stop (Cos some data might be below the empty row) and they do not need
to be deleted.

Can you please help me... All help totally appreciated.

Thanks a Great deal- Skjul tekst i anførselstegn -

- Vis tekst i anførselstegn -