CODEWITHSUNDEEP
excelvbaif-statementfor-loop
HushHoney
HushHoney

Reputation: 25

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

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

Upvotes: 1

Views: 3357

Answers (3)

Scott Craner
Scott Craner

Reputation: 152505

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.

Upvotes: 3

Darren Bartrup-Cook
Darren Bartrup-Cook

Reputation: 19782

@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.

Upvotes: 0

user6028892
user6028892

Reputation:

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

Upvotes: 0

Related Questions