delete rows macro

neills

Board Regular
Joined
May 6, 2002
Messages
104
Hi

I want to delete the same row of cells on three different sheets if a cell on that row in the first sheet is empty.
Here is what I have upto now

Dim Row As Long, Mpty As Long, Score As Long

Row = 3
Mpty = 3
Score = 0

Do
If Range("P" & Mpty) >= 1 Then
With ActiveSheet
Rows("Row:Row").Select
Selection.Delete shift:=xlUp
Sheets("Sheet1 (2)").Select
Rows("Row:Row").Select
Selection.Delete shift:=xlUp
Sheets("Sheet1 (3)").Select
Rows("Row:Row").Select
Selection.Delete shift:=xlUp
End With
End If
Row = Row + 1
Mpty = Mpty + 1
Score = Score + 1

Loop Until Score = 40



The problem is on the
Rows("Row:Row").Select
line but I just dont know how to rectify.

Any help


Neill
 

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.
Hi neills, the issue with the line Rows("Row:Row").Select is that the quotes make this a string so Excel sees this as words and cant find an address called Row:Row.

Instead you do this...
Rows(Row & ":" & Row).Select

However, you seem to be just wanting to delete from row 3 to 43 on three sheets? If so then heres an example. Just subsitute the sheet names for your ones. As you can see you dont need to select things to do something with them.

Code:
Sub Test2()
Worksheets("Sheet1").Rows("3:43").EntireRow.Delete
Worksheets("Sheet2").Rows("3:43").EntireRow.Delete
Worksheets("Sheet3").Rows("3:43").EntireRow.Delete
End Sub

hth
 
Upvote 0
Thanks Parry,

That was what I was after but it still does not act how i thought it would

I only want it to delete the row if for example cell P30 >=1 then delete row 30

from the rows 3 to 43

Neill
 
Upvote 0
Doh! I missed that bit sorry. :oops:

I guess your going to have to loop through the cells then. Personally I would loop through each sheet separately but theres nothing wrong with your approach.

You can cut down on the amount of code and avoid selecting sheets etc by joining some lines together,

These three lines...
Code:
Sheets("Sheet1 (2)").Select
Rows(Row & ":" & Row).Select
Selection.Delete shift:=xlUp

Can be replaced by...
Code:
Sheets("Sheet1 (2)").Rows(Row & ":" & Row).EntireRow.Delete
 
Upvote 0
Thanks Again Parry

That should speed things up a bit and i have just found out what else was going wrong and puzzling me was that i had put >= instead of < and it kept deleting the rows i wanted to keep
BIG DOH

now it it near enough sorted

Neill
 
Upvote 0
Now looks like

Dim Row As Long, Score As Long

Row = 3
Score = 0

Do
If Range("P" & Row) < 1 Then

Sheets("Sheet1").Rows(Row & ":" & Row).EntireRow.Delete
Sheets("Sheet1 (2)").Rows(Row & ":" & Row).EntireRow.Delete
Sheets("Sheet1 (2)").Rows(Row & ":" & Row).EntireRow.Delete
Sheets("Sheet1").Select

Else: Row = Row + 1
End If


Score = Score + 1

Loop Until Score = 40



And works well

Thanks all
 
Upvote 0
Very slick, well done Neill. :biggrin:

As another suggestion you may want to put the Sheets("Sheet1").Select
line outside of the loop as your selecting it every time.
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,208
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top