Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 - |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Help with deletion of Data in multiple | Excel Programming | |||
Automatic deletion of specific cells from multiple sheets | Excel Worksheet Functions | |||
Multiple Sheets (Need to create 500 individual sheets in one workbook, pulling DATA | Excel Worksheet Functions | |||
Deletion of rows where a value is satisfied for all the sheets | Excel Programming | |||
how do i undo deletion of sheets | Excel Worksheet Functions |