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

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"

tactps

Well-known Member
Joined
Jan 20, 2004
Messages
3,460
Instead of the row:row line, use:

ActiveCell.EntireRow.Delete

:biggrin:
 

parry

MrExcel MVP
Joined
Aug 20, 2002
Messages
3,355
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
 

neills

Board Regular
Joined
May 6, 2002
Messages
104
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
 

parry

MrExcel MVP
Joined
Aug 20, 2002
Messages
3,355

ADVERTISEMENT

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
 

neills

Board Regular
Joined
May 6, 2002
Messages
104
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
 

neills

Board Regular
Joined
May 6, 2002
Messages
104
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
 

parry

MrExcel MVP
Joined
Aug 20, 2002
Messages
3,355
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.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,151,885
Messages
5,766,936
Members
425,388
Latest member
Cave_Johnson

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
Top