Macro to delete rows in multiple worksheets within workbook

[kornstalker]

Hey all,

I would like to have a macro that would allow me to delete all rows, except row 1, in multiple worksheets, but not all worksheets in the workbook. An example of the worksheets I want to delete from are DB, KA, TJ. Like I said I'd like to keep row 1, and then delete everything else when I run it. There will only ever be of max of maybe 150 entries in those worksheets at any given time, just in case that helps lighten the load of the macro. I've seen some similar stuff but not exactly what I'm after. Thanks

[walruseggman]

Since I don't know the rule for if a sheet gets rows deleted or not, I guess you'll just have to hardcode which sheets you want rows deleted. Here's an example for the first sheet, it assumes column A has data in it all the way to the last row you want deleted. Can be adjusted as necessary. Or you could just put in something like "2:150" or whatever.

Please Login or Register  to view this content.

[Mumps1]

Try:

Please Login or Register  to view this content.

[Winon]

Hello kornstalker,

Would it be possible for you to confirm that you would prefer to keep certain Sheets intact, and if so, will it always be the same Sheets, or would you prefer to do it the other way round?

Regards

[kornstalker]

Here's the full story if it helps... This is for sorting through our "open orders" spreadsheet. Each job is a row with multiple columns. Each job has been assigned to a toolmaker, denoted by initials such as DB, DV, AJ, in one of the cells. After I run that macro, it copies the rows for the jobs that have DB in a certain cell to the worksheet titled DB. Since the information in open orders changes daily, I would like to run a macro to delete those rows, except row 1 which is the title bar, so that I can then run the other macro again without adding to the old list or deleting manually.


The same three sheets should be left intact every time, while the other 15 or so sheets would need rows deleted beginning at row 2.

Doubt you really need it but below is the macro I run to populate the other worksheets. I actually forget what the "A" is for in that code.

Sub ToolmakerSort()
Dim i As Long
Dim lr As Long
lr = Cells(Rows.Count, 1).End(xlUp).Row
For i = lr To 2 Step -1

If Range("H" & i).Value <> "" Then
On Error Resume Next
Range("H" & i).EntireRow.Copy Sheets(Range("H" & i).Value).Range("A" & Rows.Count).End(3)(2)
On Error GoTo 0
End If

Next i
End Sub

[kornstalker]

I tried the Mumps code. Appeared to work well except that it deleted the top row, row 1, on the worksheets that didn't have any other entries other than the title bar. Not sure what causes that...is it the "& last row" part in the code?

[Mumps1]

Try:

Please Login or Register  to view this content.

[kornstalker]

Thanks...That one seems to work fine. I just realized that the macro I run that copies all those rows to the other tabs, will not do so unless I have selected the main worksheet. It really doesn't matter, but why would the new one work no matter what tab/worksheet I have selected as active, while the other one require I have a certain one selected?

[Mumps1]

If you want the code to work from any worksheet, you have to make sure that each line of code that references a certain worksheet includes the full worksheet name. In your case it would be the name of the main worksheet. for example:

Please Login or Register  to view this content.

instead of:

Please Login or Register  to view this content.

[kornstalker]

The main page it pulls from is titled "Open"
This is the original macro that works but would not unless I ran it while on the "Open" worksheet tab:

Sub ToolmakerSort()
Dim i As Long
Dim lr As Long
lr = Cells(Rows.Count, 1).End(xlUp).Row
For i = lr To 2 Step -1

If Range("H" & i).Value <> "" Then
On Error Resume Next
Range("H" & i).EntireRow.Copy Sheets(Range("H" & i).Value).Range("A" & Rows.Count).End(3)(2)
On Error GoTo 0
End If

Next i
End Sub

This is what I ended up with after trying, probably incorrectly, to incorporate what you said. All I did was add ---Sheets("Open").--- in the first and third lines in the indented code section. It seems to do the same thing as before...It's really not a big deal but I figure there's a way to run it from whatever worksheet you happen to be on at the time.

Sub ToolmakerSort()
Dim i As Long
Dim lr As Long
lr = Cells(Rows.Count, 1).End(xlUp).Row
For i = lr To 2 Step -1

If Sheets("Open").Range("H" & i).Value <> "" Then
On Error Resume Next
Sheets("Open").Range("H" & i).EntireRow.Copy Sheets(Range("H" & i).Value).Range("A" & Rows.Count).End(3)(2)
On Error GoTo 0
End If

Next i
End Sub

[Mumps1]

Try:

Please Login or Register  to view this content.

You also have to define the worksheet that "lr" is on.

[kornstalker]

No luck on that one either! Oh well. It's really quite insignificant, I just noticed it and wondered if there was an easy fix. I don't want to trouble you with it anymore though ha. Thanks for the help though as the ToolmakerDelete one is just what I needed.

[Mumps1]

My pleasure.