Delete rows in Multiple Worksheets

[phong919]

Hello all,

I have about 51 worksheets in a workbook. I need to delete a couple of rows in column B. I try the following code and it's not working how i want it to. Can someone take a look and guide me in the right direction? I want to loop through all the worksheets and delete the rows.

Please Login or Register  to view this content.

[Leith Ross]

Hello phong919,

To make your posts easier to read, copy, and edit please wrap your code. I did it for you this time. Here is how you can do it next time.

How to wrap your Code
1. Select all your code using the mouse.
2. Click on the # icon on the toolbar in the Message window. This will automatically wrap the text you selected with the proper Code tags to create a Code Window in your post.
3. To do it manually...
Use the Bulletin Board Code Tags
[code]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/code] at the end of the last line.


To learn more about BB codes used in this forum, just click on the link below...

Bulletin Board Codes Tags

[phong919]

Thank you for wrapping my code.

[Leith Ross]

Hello phong919,

Try his version of your macro and let me know how it works for you.

Please Login or Register  to view this content.

[phong919]

Thank you for the code. I need to delete a couple of rows after finding
(Cells(I, "B").Value) = "19. B Missed Deliveries - Reported Month"...

so let say the loop finds the following at row 19

"19. B Missed Deliveries - Reported Month" - this would be DELETED
and the row below that should also be deleted
then the loops should also find the row with the following -
"19. C Deliveries Expected - In the Next Reporting Month" - this should be deleted and also the row below that should be deleted.

How would i accomplish that in the loop? Any guidance would be greatly appreciated.

thank you.

[JBeaucaire]

This version should accomplish the same thing without having to loop through every row one at a time. We're inserting a set of "key" formulas in an empty column and running an AutoFilter on the data, deleting the matching rows all at once.

This macro assumes the DATA starts at row 1. If Row1 is actually "titles" and not data, then you can delete the two lines of code marked in red:

Please Login or Register  to view this content.

The formula I'm inserting in an empty column is:

=IF(OR(ISNUMBER(MATCH("19. B Mis*",$B2,0)),ISNUMBER(MATCH("19. C Del*",$B2,0))),1,"x")

As you can see, rather than spelling out the whole text string, I just used the first few characters...expecting that to be enough to match accurately. If you have other cells that start with "19. B Mis" and "19. C Del" then you'll have to expand that text to more complete, I just thought it might be sufficient.

[phong919]

Thank you for the code, i'm getting run-time error '1004' cannot change part of a merged cell. does that mean i have to unmerged the cell in the row?

[JBeaucaire]

I used an empty column outside the data set, so I can't see how merged cells would be interfering with the macro.

As a rule, I don't use merged cells for this very reason, too many ways they interfere with advanced sheet manipulation.

[phong919]

There are also hidden sheets. How can i specified which sheet tab to start the row deletion. Is there a way to select the whole row even with merged cells and then delete the rows?

[JBeaucaire]

If it's just a couple of sheets, you can exclude them by name:

Please Login or Register  to view this content.

If you want the macro to skip any sheets that are hidden, then you can test for the visibility of the sheet:

Please Login or Register  to view this content.

========
I don't use merged cells.

[phong919]

Hello thanks for the response. I was just debugging the code and it's not deleting the right row. Can you tell me what each row is suppose to be doing. I see that populates column T with "key" and the rest or "x". but i do not see the rows i want to be deleted. My apologies for all these questions, as i'm a newbie to excel vba. thank you.

[phong919]

anybody can assist me?

[JBeaucaire]

The formula looks for "19. B" and "19. C" in each the cell "B" on the same row and the row above. If it is there, it puts a 1 in that cell, if not then it gets an "x". Then we filter the column for 1s and delete them all.

Post up your workbook so we can see what problem you're having. Make sure the macro is already in the workbook so I can see where you put it.

Click GO ADVANCED and use the paperclip icon to post up your workbook.

[phong919]

Here is what the template looks like...

I've highlighted the rows that needs to be deleted in yellow. Also there are columns that i've highlighted in yellow that needs to be deleted. Any guidance will be great.

thank you.

[JBeaucaire]

Post up your workbook so we can see what problem you're having. Make sure the macro is already in the workbook so I can see where you put it.

Hmm...weird, I can't find the macro in your sheet. Also, no hidden worksheets I can find.

[JBeaucaire]

Here you go, something weird about your strings so I just changed the "key" formula altogether.

Please Login or Register  to view this content.

Based on your sheet, is there always only going to be ONE cell with "19. B Missed Deliveries" and ONE cell with "19. C Deliveries Expected" on each sheet? If so, then the macro can be even faster with a simple one-shot .FIND command.

[JBeaucaire]

This is fastest of all to find ONE instance of each string on each VISIBLE sheet:

Please Login or Register  to view this content.

[phong919]

i removed the hidden sheets. and the macro has been added. sorry about that. it's been crazy here....

[phong919]

Would the same code work for the columns that i highlighted in yellow to the right? that's to delete the columns.

[phong919]

I just tried the code you suggested....and it worked for the first sheet but when it got to the second sheet, i get the following error...

Run-time error '91'
Object variable or with block not set...

[JBeaucaire]

Delete them based on what criteria? If none, you can get the code for something consistent and static by simply turning on the macro recorder and recording you doing it by hand...

Simple code delete those 5 columns would be:

Please Login or Register  to view this content.

Add that to the bottom of the macro before the sub ends.

[JBeaucaire]

I just tried the code you suggested....and it worked for the first sheet but when it got to the second sheet, i get the following error...

Run-time error '91'
Object variable or with block not set...

I truly thought the values you were searching actually existed on each of the sheets. No? Ok, then:

Please Login or Register  to view this content.

[phong919]

Hello Jbeaucaire,

THank you for the update. So i ran the code and it works for 1 sheet tab. for some reason the other sheet tabs didn't delete the rows and columns, so i debug it and the code only works when i select the other tabs. Am i doing something wrong? Do i need to add worksheet.activate or something? thank you.

[JBeaucaire]

The code as posted worked for me (or so it did as I remember, it's been many days).

Feel free to post a new workbook showing the macro installed as you're trying to use it and let me give it a look directly.

[phong919]

Hey Jbeaucaire,

Do you know how i can incorporate another part of code to copy certain rows and pasting it on a master sheet? Please assist in anyway.

Thank you.

[JBeaucaire]

Yes, I know many ways. Let's see the code you want to incorporate. Be sure to explain what it is doing, too.

[phong919]

hello Jbeaucaire,

i'm using the following code.

For Each ws In ActiveWorkbook.Worksheets
ws.Activate
Strdate = ws.Range("B28").Value

lngLastRow = Range("B" & Rows.Count).End(xlUp).Row



For Each cell In Range("B29:B" & lngLastRow)

x = cell.Row
Rows(x).Copy
Sheets("sheet1").Cells(x, 1).Insert Shift:=xlDown

Next cell

Application.CutCopyMode = False


Next

but it seems like it's just copying over the data when it loops through each sheet. Please advise. thank you.

[JBeaucaire]

Be sure to read through the Forum Rules so you can use and follow them effectively. For instance, you'll need to edit that post above and put code tags around that code you used. (Like I did below...)

==========
You didn't show the full code, so I can't speak to the StrDate usage, but for the code you did show, this is how I would write that:

Please Login or Register  to view this content.

[phong919]

Thank you for the code. but i think that's what i was looking for. let me try to explain it and also attach a sample sheet.

In the attach sample starting from row 28 in column b is the header which is highlighted in light grey color. i need to loop through the the dark grey color until the end of that section and copy each rows to the master sheet. Once that is dones, i would like it to go to the next section where the headers are the same as the first section. i would like the macro to copy all the rows in the dark grey section and copy it to the master sheet. all the rows can range from just 1 to many rows. do i need to different part of code for it? or is there a way to do a search and find. Thank you.

[JBeaucaire]

Please fix post #27 as per forum rules. I have some more questions which I'll post after you've done that.