Trying to use the “Cells Property” to select multiple ranges simultaneously

[Jeremy4110]

Hi,

I'm trying to copy multiple ranges using the cell properties. I have tried a bunch of alternate combinations of the second macro below but have been unable to get any to work.

The first macro was a recording and it works, but it will always be the same four ranges, I wanted to try and use the “Cell Property” so that I could use a variable location like “MaxRow = ActiveSheet.UsedRange.Rows.Count” because it allows changes.

Any help is greatly appreciated.

Thanks,
Jeremy

This is the recorded macro.

Sub Macro1()
   
    Range("A1:A2,B1:K2,A14:A19,B14:K19").Select
    Selection.Copy
 
    Sheets.Add After:=Sheets(Sheets.Count)
    Range("A1").Select
    ActiveSheet.Paste
   
    Cells.Select
    Cells.EntireColumn.AutoFit
    Application.CutCopyMode = False
   
    Range("A1").Select
   
End Sub

This the macro that I have not been able to get to work

Sub TESTSample()
 
    Dim rng1 As Range, rng2 As Range
 
    Set rng1 = Range(Cells(1, 1), Cells(2, 1))  '("A1:A2")
    Set rng2 = Range(Cells(1, 2), Cells(2, 11)) '("B1:K2")
   
    rng1.Select 'This works
    rng2.Select 'This works
   
    'Range("A1:A2,B1:K2,A14:A19,B14:K19").Select    'This is the ultimate goal and it works but I need to use variable ranges rather than fixed ranges
   
    Range(Cells(rng1), Cells(rng2)).Select          'This is an example that does NOT work
 
End Sub

 

[pvr928]

Re: Trying to use the “Cells Property” to select multiple ranges simultaneously, Please Help!!!

Hi Jeremy4110

Perhaps replace

Range(Cells(rng1), Cells(rng2)).Select

with

Union(rng1, rng2).Select

I question why you are using the .Select method. There is little need to use .Select in VBA.

Cheers

pvr928

 

[Jeremy4110]

Re: Trying to use the “Cells Property” to select multiple ranges simultaneously, Please Help!!!

Good morning pvr928,

Thank you for your help, Your recommendation worked perfectly.

To answer your question, I am not that good with macros; I am trying to learn new methods and ways to do different things. I do know that I don’t really have to use the .Select method very much but since I am still learning my code rarely works right on the first try so I use the select method for testing purposes to confirm my ranges works before trying to shorten it and make it more elegant.

Believe it or not, I tried a lot of combinations yesterday using .Union and .Intersect but I never considered it to be that simple. I say simple in reference to the code being that short, hence the failed “Range(Cells(rng1), Cells(rng2)).Select” attempt.

The macro that I am attempting to write is for a “For, Next” range to print multiple reports.

Thanks,
Jeremy