CODEWITHSUNDEEP
vbaexcel
Joe Gay
Joe Gay

Reputation: 47

How to find and select the last column in VBA?

I am trying to create an excel macro which finds the last column of a sheet and then selects the entire column. However, this column will always be different- some days it will be column 'H', other days will be column 'GX' as the data in the sheet is constantly updated. So far I have seen how you can find the last column and then delete it, but it specifically refers to that certain column once the macro runs again. I need it to always refer to the last column, no matter what column that may be. Thanks!

Here is the code. I am new to VBA, etc. and this was created through the macro recorder and other things I found online so bear with me!

`Sub Macro11()
Sheets("Sheet25").Cells(1, 1).Activate
ActiveCell.SpecialCells(xlLastCell).Select
lastCol = ActiveCell.Column
Columns("W:W").Select
Selection.Delete Shift:=xlToLeft
End Sub`

Upvotes: 1

Views: 19881

Answers (1)

Santosh
Santosh

Reputation: 12353

Here is the sample code
Avoid using Select /Activate in your code. To know why refer this link

Sub Macro11()
    Dim LastCol As Long

    With ThisWorkbook.Sheets("Sheet25")
        LastCol = .Cells(1, .Columns.Count).End(xlToLeft).Column
        .Columns(LastCol).Delete
    End With

End Sub

Upvotes: 3

Related Questions