VBA to select multiple row ranges and then delete them all at once

[SlinkRN]

This seems like it would be so easy but I just can't get it to work yet. I need to do this:
sheets("sheet1").range("4:136,158:198,250:289,316:800").select
selection.delete shift:=xlup

EXCEPT that I need to use variables to find the row numbers.
Row 4 is always row 4 so I can use that number.
Row 136 will actually be the variable DeleteRow
Row 158 will actually be the variables DeleteRow + EndDeleteRow
Row 198 will actually be the variable DeleteRow2
Row 250 will actually be the variables DeleteRow2 + EndDeleteRow2
Row 289 will actually be the variable DeleteRow3
Row 316 will actually be the variables DeleteRow3 + EndDeleteRow3
Row 800 is always row 800 so I can use that number.

I've tried:
sheets("sheet1").range("4:" & DeleteRow,DeleteRow + EndDeleteRow & ":" & DeleteRow2,DeleteRow2 + EndDeleteRow2 & ":" & DeleteRow3,DeleteRow3 + EndDeleteRow3 & ":800").select
selection.delete shift:=xlup
but I get an error saying "Run-time error '450' Wrong number of arguments or invalid property assignment"

What am I missing? Thanks so much! Slink

 

[Peter_SSs]

Give this a try with a copy of your workbook

Sheets("sheet1").Range(Replace(Replace(Replace(Replace(Replace(Replace("4:@,#:$,%:^,&:800", _
  "@", DeleteRow), "#", DeleteRow + EndDeleteRow), "$", DeleteRow2), "%", DeleteRow2 + EndDeleteRow2), "^", DeleteRow3), "&", DeleteRow3 + EndDeleteRow3)).Delete

 

[Akuini]

Another option:

With Sheets("sheet1")
Union(.Range("A4" & ":A" & DeleteRow), .Range("A" & DeleteRow + EndDeleteRow & ":A" & DeleteRow2), _
.Range("A" & DeleteRow2 + EndDeleteRow2 & ":A" & DeleteRow3), .Range("A" & DeleteRow3 + EndDeleteRow3 & ":A800")).EntireRow.Delete
End With

 

[Peter_SSs]

If using the union idea (good idea), to me it makes sense (& shorter code) to eliminate the column A references and go straight to entire rows.

With Sheets("sheet1")
Union(.Rows("4:" & DeleteRow), .Rows(DeleteRow + EndDeleteRow & ":" & DeleteRow2), _
.Rows(DeleteRow2 + EndDeleteRow2 & ":" & DeleteRow3), .Rows(DeleteRow3 + EndDeleteRow3 & ":800")).Delete
End With

 

[SlinkRN]

Thanks so much Peter and Akuini!! It works great!!

 

[Peter_SSs]

Thanks so much Peter and Akuini!! It works great!!

You're welcome. Glad we could help. Thanks for the follow-up.

 

[Trebor76]

Little late but as the Range function accepts a string this worked me (correct me if I'm I wrong):

Sheets("Sheet1").Range("4:" & DeleteRow & "," & DeleteRow + EndDeleteRow & ":" & DeleteRow2 & "," & DeleteRow2 + EndDeleteRow2 & ":" & DeleteRow3 & "," & DeleteRow3 + EndDeleteRow3 & ":800").Delete shift:=xlUp

Regards,

Robert

 

[Peter_SSs]

the Range function accepts a string

Post #2 also used a (similar) string, just in a slightly different way.

 

[SlinkRN]

Thank you Trebor, that does work!! I had written something like that to start with, but after looking at yours I realize now that I was forgetting to put the commas in quotes and using ampersands around them!!! Thanks for solving that mystery for me!! I was glad to learn about the Union thing too though. I love that Excel has so many ways to get things done

 

[Trebor76]

Thank you Trebor, that does work!! I had written something like that to start with, but after looking at yours I realize now that I was forgetting to put the commas in quotes and using ampersands around them!!! Thanks for solving that mystery for me!!

You're welcome

I was glad to learn about the Union thing too though. I love that Excel has so many ways to get things done

That is true. I suppose the key is to use the most efficient way