Selecting similar range with VBA in 2 or more sheets

[Viper147]

Hi there,

I am a relatively new user with VBA and have survived thus far with visiting billiant forums like this one, or alternatively playing around in VBA until I manage to solve the problem myself.
However this problem has me stumped and I need some help please.
The code is supposed to select the same variable range in 2 sheets and delete the entire rows in these sheets. I get a "Select method of Range class failed" error on the red line of code below.
As I mentioned, new user, so please be gentle.
Thanks

Sub Reset()

Dim Range1 AsRange
Dim Range2 AsRange
Dim Range3 AsRange
Dim Range4 AsRange
Dim Range5 AsRange
Dim Range6 AsRange
Dim Range8 AsRange
Set Range1 =Range("C4:C" & Range("CurrentDeals") + 2)
Set Range2 =Range("C" & Range("CurrentDeals") + 7 &":C" & Range("CurrentDeals") * 2 + 5)
Set Range3 =Range("C" & Range("CurrentDeals") * 2 + 12 &":C" & Range("CurrentDeals") * 3 + 10)
Set Range4 =Range("C" & Range("CurrentDeals") * 3 + 12 &":C" & Range("CurrentDeals") * 4 + 10)
Set Range5 =Range("C" & Range("CurrentDeals") * 4 + 12 &":C" & Range("CurrentDeals") * 5 + 10)
Set Range6 =Range("C" & Range("CurrentDeals") * 5 + 12 &":C" & Range("CurrentDeals") * 6 + 10)
Set Range7 =Range("C" & Range("CurrentDeals") * 6 + 12 &":C" & Range("CurrentDeals") * 7 + 10)
Set Range8 =Range("C" & Range("CurrentDeals") * 7 + 12 &":C" & Range("CurrentDeals") * 8 + 10)

Sheets("Forecast Income").Select
Union(Range1,Range2, Range3, Range4, Range5, Range6, Range7, Range8).Select
Selection.EntireRow.Delete
Range("A1").Select

Sheets("Forecast Expense").Select
Union(Range1,Range2, Range3, Range4, Range5, Range6, Range7, Range8).Select
Selection.EntireRow.Delete
Range("A1").Select

Sheets("Instructions").Select
Range("M3").Select
Selection.ClearContents
Range("A1").Select
End Sub

 

[Kamolga]

Hi,

To select a similar range on 2 sheets, you can select both beforehand by using

sheets(Array("Forecast Income","Forecast Expense")).select

If instead of the read line, you type Range1.select and delete the rest of the macro, does it work? Does it select Range1
If it does not, we identified the issue try something like

[LEFT][COLOR=#101094][FONT=Consolas]Dim[/FONT][/COLOR][COLOR=#303336][FONT=Consolas] Range1 [/FONT][/COLOR][COLOR=#101094][FONT=Consolas]As[/FONT][/COLOR][COLOR=#303336][FONT=Consolas] Range[/FONT][/COLOR][COLOR=#303336][FONT=Consolas]:[/FONT][/COLOR][COLOR=#101094][FONT=Consolas]Set[/FONT][/COLOR][COLOR=#303336][FONT=Consolas] Range1 [/FONT][/COLOR][COLOR=#303336][FONT=Consolas]=[/FONT][/COLOR][COLOR=#303336][FONT=Consolas] ActiveSheet[/FONT][/COLOR][COLOR=#303336][FONT=Consolas].[/FONT][/COLOR][COLOR=#333333][FONT=Calibri][SIZE=3][COLOR=#000000]Range("C4:C" & Cells(Range("CurrentDeals").row + 2,3))[/COLOR][/SIZE][/FONT][/COLOR][/LEFT]

And try to select it to see if it works


If it does
I would then try something like

Dim DelRange as Range
[LEFT][COLOR=#0000ff][FONT=Menlo]Set[/FONT][/COLOR][COLOR=#000000][FONT=Menlo] DelRange = Union([/FONT][/COLOR][COLOR=#a31515][FONT=Menlo]Range1[/FONT][/COLOR][COLOR=#000000][FONT=Menlo], [/FONT][/COLOR][COLOR=#a31515][FONT=Menlo]Range2[FONT=Consolas][COLOR=#b00000],...[/COLOR][/FONT][/FONT][/COLOR][/LEFT]
[FONT=Consolas][COLOR=#b00000][LEFT]) 
DelRange.entirerow.delete
[/LEFT]
[/COLOR][/FONT]

 

[steve the fish]

You could go like this:

Set myRng = Union(Range1, Range2, Range3, Range4, Range5, Range6, Range7, Range8)

Sheets("Forecast Income").Select
Range(myRng.Address).EntireRow.Delete Shift:=xlUp
Range("A1").Select

Sheets("Forecast Expense").Select
Range(myRng.Address).EntireRow.Delete Shift:=xlUp
Range("A1").Select

 

[Viper147]

Hi guys,
Thanks so much for the prompt response with this, I really appreciate the help. I went with the code from Steve and it works perfecty (apart from a small error on my side that I picked up after the code actually worked).