Macro for selecting every other row

[learninman]

Hi,
Im new to VBA and have come into a few instances where I have tried, unsuccessfully, to create a macro that will select every other row and then copy that selection. What I have been settling for is holding control and selecting whatever rows I need to copy but I am curious to know if it is possible to code this.
please let me know,
thanks

 

[VoG]

Try like this

Sub OtherRow()
Dim LR As Long, i As Long, r As Range
With Sheets("Sheet1")
    LR = .Range("A" & Rows.Count).End(xlUp).Row
    Set r = .Rows(1)
    For i = 3 To LR Step 2
        Set r = Union(r, .Rows(i))
    Next i
End With
r.Copy Destination:=Sheets("Sheet2").Range("A1")
End Sub

 

[learninman]

Thanks VoG,
works great!

 

[learninman]

Hey,
two quick questions that came up out of curiosity.
Well first of all, is there any differenece with instead of writing LR = .Range("A" & Rows.Count).End(xlup).Row
and LR = .Range("A1").End(xldown).Row ?
they produce the same results, and I am just curious, not sure if this is stupid question

also could a simillar code be executed for columns instead of rows, for instance in Row 1, selecting and after copying every other column in a row?

thanks again for your previous response

 

[VoG]

LR = .Range("A1").End(xldown).Row

will give you the last filled row looking from row 1 down.

LR = .Range("A" & Rows.Count).End(xlup).Row

will give you the last filled row from the bottom of the sheet looking up.

The code to copy alternate columns shouold be entirely analogous.

 

[learninman]

I thought i could do it using a bunch of different methods and have not been able to make it work. I tried making LR = .Range("IV1").End(xlleft).column but that didnt work and neither did LR = .Range("A1").End(xlright).column

I keep getting error message in this line of the code: applictaion defined or object defined error

 

[learninman]

hey sorry,
just figured out it out, sorry for not searching enough. xltoright must be used rather than xlright

 

[learninman]

This questions is also out of curiosity, and if it is bothersome do not reply, just trying to learn and you seem very knowledgable

Is there a way to do with while just selecting every other cell in column A, not the entire rows. I tried this
Code:
Sub OtherRow()
Dim LR As Long, i As Long, r As Range
With Sheets("Sheet1")
LR = .Range("A" & Rows.Count).End(xlUp).Row
Set r = Cells(1, 1)
For i = 3 To LR Step 2
Set r = Union(r, Cells(i, 1))
Next i
End With
r.Copy Destination:=Sheets("Sheet2").Range("A1")
End Sub

but I understand that the range you copy and the range you paste in must be the same size and shape.

 

[Rick Rothstein]

Try like this

Sub OtherRow()
Dim LR As Long, i As Long, r As Range
With Sheets("Sheet1")
    LR = .Range("A" & Rows.Count).End(xlUp).Row
    Set r = .Rows(1)
    For i = 3 To LR Step 2
        Set r = Union(r, .Rows(i))
    Next i
End With
r.Copy Destination:=Sheets("Sheet2").Range("A1")
End Sub

Just to follow up on Peter's code... this will work fine for a smallish number of rows of data, but that Union method will really start to slow things down as the number of rows needing to be processed increases. I'd guess this would become noticeable by about 1000 rows, but surely by 5000 rows. Here is alternate code that should remain snappy no matter how many rows of data you are processing...

Sub DeleteEveryOtherRow()
  Dim X As Long, LastRow As Long, UnusedCol As Long, OneNull() As Variant
  Const StartRow As Long = 1
  With Worksheets("Sheet1")
    LastRow = .Cells.Find(What:="*", SearchOrder:=xlRows, _
              SearchDirection:=xlPrevious, LookIn:=xlFormulas).Row
    UnusedCol = .Cells.Find(What:="*", SearchOrder:=xlByColumns, _
                SearchDirection:=xlPrevious, LookIn:=xlFormulas).Column + 1
    ReDim OneNull(1 To LastRow)
    For X = StartRow To LastRow Step 2
      OneNull(X) = 1
    Next
    Application.ScreenUpdating = False
    .Cells(1, UnusedCol).Resize(LastRow).Value = WorksheetFunction.Transpose(OneNull)
    If StartRow > 1 Then .Cells(1, UnusedCol).Resize(StartRow - 1).Clear
    With .Cells(StartRow, UnusedCol).Resize(LastRow - StartRow + 1).SpecialCells(xlCellTypeConstants)
      .Clear
      .EntireRow.Copy Worksheets("Sheet2").Range("A1")
    End With
    Application.ScreenUpdating = True
  End With
End Sub

Note I provided a StartRow constant (the Const statement) to allow you to specify which row to begin the alternate line of copy from. I set it to 1 in my code above to match the output from Peter's code; but, as I said, you can set it to any row number and the copy process will start from that row onward.

 

[Rick Rothstein]

Sub DeleteEveryOtherRow()[/QUOTE]
Sorry about using that for the macro name (hopefully it did not scare you off from using the code)... I cannibalized this macro from one I had laying around and forgot to change the name in the process. Simply change the name to something more meaningful to your usage, like CopyEveryOtherRow for example.