How to select the entire column starting from row 3 - vba

Question

Hey Guys here is my code it is working but instead of selecting the entire column I want to select all of the column buter after row 3, for example I want to select all of column b but after B3 I hope that makes sense.

any ideas?

Sub HorizontalLoop()
Dim lCol As Long

Sheets("output").Select
For lCol = 1 To 100

Dim inputrange As String
If Not IsEmpty(Cells(lCol).Value) Then
inputrange = Cells(1, lCol).Value

Cells(1, lCol).EntireColumn.Select
Selection.Copy
Sheets("input").Select
ActiveSheet.range(inputrange).Select
ActiveSheet.Paste
Sheets("output").Select
End If
Next lCol
End Sub

Any help will be very much appreciated :) Thanks H

This has been asked on SO many times. Or you can find it on google XD — findwindow, Apr 05 '16 at 15:47
I apologise if it's been asked so many times but when your new to VBA sometimes it's hard to understand in others peoples code! — HushHoney, Apr 06 '16 at 21:12
So sorry for being annoying! But thanks everyone that's helped — HushHoney, Apr 06 '16 at 21:12

score 3 · Accepted Answer · edited May 23 '17 at 12:31

Here is your code (with your answer) a little cleaned up:

Sub HorizontalLoop()
Dim lCol As Long
Dim inputrange As String
With Sheets("output")
    For lCol = 1 To .Cells(1, .Columns.Count).End(xlToLeft).Column
        If Not IsEmpty(.Cells(1, lCol).Value) Then
            inputrange = .Cells(1, lCol).Value
            .Range(.Cells(3, lCol), .Cells(.Rows.Count, lCol)).Copy Sheets("input").Range(inputrange)
        End If
    Next lCol
End With
End Sub

Try to avoid using .Select and .Activate.

Also it is better to ensure proper parentage when using two sheets. The With Block with its corresponding . allows this.

score 0 · Answer 2 · answered Apr 05 '16 at 15:57

0

Change A1 to your destination range

Sub test()

    Sheets("input").Range(Cells(3, "B") _
        , Cells(Sheets("input").Cells.Rows.Count, "B")).Copy Sheets("output").Range("A1")

End Sub

answered Apr 05 '16 at 15:57

score 0 · Answer 3 · answered Apr 05 '16 at 15:57

@findwindow's right - has been asked so many times.

ThisWorkbook.Worksheets("output").Cells(1, lcol).EntireColumn.Resize(Rows.Count - 3).Offset(3).Select

There's no need to select the sheet though:

Create a new sheet and have that selected then use this code:

ThisWorkbook.Worksheets("output").Cells(1, lcol).EntireColumn.Resize(Rows.Count - 3).Offset(3).Copy _
    Destination:=ThisWorkbook.Worksheets("input").Cells(4, 2)

It will copy from output to input without needing to have the sheet selected - just reference the sheets in your code.

How to select the entire column starting from row 3 - vba

3 Answers3