1

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`
pnuts
  • 58,317
  • 11
  • 87
  • 139
Joe Gay
  • 47
  • 1
  • 2
  • 7
  • 1
    There are ample examples online for your post. Can you share the code you tried? – Santosh Jul 18 '13 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 Jul 18 '13 at 18:06
  • 1
    `LastCol = Cells(i, Columns.Count).End(xlToLeft).Column where i is the row` .Refer this [link](http://www.rondebruin.nl/win/s9/win005.htm) – Santosh Jul 18 '13 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 Jul 18 '13 at 18:18
  • possible duplicate of [excel vba finding the last column with data; Excel VBA](http://stackoverflow.com/questions/11926972/excel-vba-finding-the-last-column-with-data) – whytheq Jul 19 '13 at 12:27

1 Answers1

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
Community
  • 1
  • 1
Santosh
  • 12,175
  • 4
  • 41
  • 72
  • 1
    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 – Joe Gay Jul 18 '13 at 18:28