Solution not needed - just clarity on a piece of this code!

[JamesonMH]

Hi folks,

I got the below code online somewhere a a few months back and its recently come in handy for something I'm doing (copying every other row and pasting it elsewhere).

However, I'm new to VBA and do not understand 1 thing (which is a problem since I'll need to troubleshoot): why does "Set RowSelect =" appear twice??

I can't wrap my head around why it's Set as MyRange.Rows(2), but then 2 lines later its Set as something else, but in the end it all works perfectly. Don't you Set a variable to an object once and be done with it?

Any explanation on that piece would be super appreciated. Cheers, James.

Sub Copy_every_nth_row()
Dim MyRange As Range, RowSelect As Range, i As Integer, LR As Long
LR = Range("A" & Rows.Count).End(xlUp).Row
Set MyRange = Range("A1:A" & LR)
Set RowSelect = MyRange.Rows(2)


For i = 2 To LR Step 2
Set RowSelect = Union(RowSelect, MyRange.Rows(i))
Next i
RowSelect.Copy Range("D2")


End Sub

 

[Trebor76]

Hi James,

The code is building a range called RowSelect. To do this the code uses the VBA only function called UNION which needs a range to already have something in it (this is what your first highlight is for which is Row 2 in this case) before more can be added (appended) to it (this is what the second highlight is doing by adding each Row "i").

Though you define a variable once you don't necessarily only assign something to it once. For instance you could use the variable LR to find the last Row in another column apart from column A as it's presently doing.

HTH

Robert

 

[JamesonMH]

Thanks Robert for the quick reply and the clear explanation, it definitely answers some of my questions.

The only question I still have is why can't you call the 2nd instance of "Set RowSelect" something else ... like "Set ABC = (Union,RowSelect,MyRange.Rows(i))"?

I just tried it but when I execute only the first cell will gets pasted in col D instead of every 2nd row like its supposed to.

Sorry if this is a really basic question, I thought my fairly good Excel skills would help me in learning VBA, but I'm finding that's not the case

 

[Trebor76]

I'm not too sure what you mean or what you're after (is ABC a range variable??) but here's another slightly different way to do the same that I wrote with some notes (hope it helps):

Option Explicit
Sub Copy_every_nth_row()

    Dim RowSelect As Range, MyRange As Range
    Dim i As Long, LR As Long
    
    Application.ScreenUpdating = False
    
    LR = Range("A" & Rows.Count).End(xlUp).Row
    Set MyRange = Range("A1:A" & LR)

    For i = 2 To LR Step 2
        'If 'RowSelect' has nothing in it, then...
        If RowSelect Is Nothing Then
            '...start it by adding the first row number to it
            Set RowSelect = MyRange.Rows(i)
        'Else...
        Else
            '...append the next row number to it
            Set RowSelect = Union(RowSelect, MyRange.Rows(i))
        End If
    Next i
    
    RowSelect.Copy Range("D2")
    
    Application.ScreenUpdating = True

End Sub

Robert

 

[JamesonMH]

Sorry for the confusion (I meant ABC as a variable) .... I was just asking since the 1st variable (in original code) is Set RowSelect=MyRange.Rows(2), why can't the next instance of RowSelect (i.e. RowSelect=Union....) be declared as a differently named variable so it's not as confusing....such as RowSelectABC=Union...?

I get my question probably sounds silly to a more advanced VBA user that just 'gets it', but from a novice like me I can't comprehend why the there needs to be a 2nd instance of RowSelect.

And thanks very much for the alternative code. I'll go through it in the AM with a fresh set of eyes - it'll probably be more intuitive.

 

[Trebor76]

You only want to have a single range for ease of output in this case. If you introduce a second range you'll probably have to initially set it and then use UNION to append it. Seems double handling to me.

 

[JamesonMH]

Ok, that makes sense - I find VBA code super intuitive most times (at least the fundamentals) but times like these its tricky.

Thanks again for your insight on this and sharing a different code, Robert. I'm way ahead on understanding this concept because of your help. Cheers!

James

 

[Trebor76]

You're welcome. Glad it helped