How to find and select the last column in VBA?

Question

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`

There are ample examples online for your post. Can you share the code you tried? — Santosh
– Santosh, Commented Jul 18, 2013 at 17:54
Yeah. I am new to VBA, etc. so I kind of used other posts I saw mixed with code from the record macro feature in excel. 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 — Joe Gay
– Joe Gay, Commented Jul 18, 2013 at 18:06
LastCol = Cells(i, Columns.Count).End(xlToLeft).Column where i is the row .Refer this link — Santosh
– Santosh, Commented Jul 18, 2013 at 18:10
Doesn't that just find the last column in a certain row? I'm looking for it to select the entire column in the entire sheet, not just tell me how many columns there are in a certain row. Any ideas? — Joe Gay
– Joe Gay, Commented Jul 18, 2013 at 18:18
possible duplicate of excel vba finding the last column with data; Excel VBA — whytheq
– whytheq, Commented Jul 19, 2013 at 12:27

Community · Accepted Answer · 2017-05-23 11:58:02Z

3

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

edited May 23, 2017 at 11:58

CommunityBot

11 silver badge

answered Jul 18, 2013 at 18:19

Santosh

12.4k4 gold badges43 silver badges74 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Joe Gay Over a year ago

Perfect, thank you! If I wanted to do something similar- (find a column that is constantly changing and delete it) but instead of having it look up and delete the last column, have it use a 'find' (like ctrl+f) function to find a word in the spreadsheet and then select the column, would that be similar? Its another problem that I've yet to solve haha

Collectives™ on Stack Overflow

How to find and select the last column in VBA?

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related