VBA Go to cell after last column with data

[Jenya]

Hello guys,

I have a massive spreadsheet which in which the number of columns is going to change each time I run it. I want to label the column after the last column with data and put formula in it. I have searched for a solution but there are examples for dynamic row numbers only. I found this code which finds the last column in a spreadsheet but couldn't write the code which takes me to the next blank cell/column for the first row. Could you please help me with that. Thank you

Sub Last_Collumn()

Range("A1").End(xlToRight).<wbr>Select

End Sub

 

[HalfAce]

Hi,
A couple things to take into account with what you're doing.
First, a small change to what you posted that will do what you've asked.

Sub Last_Collumn()
Range("A1").End(xlToRight)[COLOR=#ff0000].Offset(, 1)[/COLOR].Select
End Sub

It should be pointed out that that will work as long as there are no blanks in row 1 between column A and the last column with data.

A more dynamic approach would be to start from the right and work left to the last column with data. This way it doesn't matter if there are any blanks or not. (ie.)

Sub Last_Column()
Cells(1, Columns.Count).End(xlToLeft).Offset(, 1).Select
End Sub

And another way (the way I'd do it...) would be to declare a variable for the last column and then reference that throughout the code. (ie.)

Sub Last_Column()
Dim LstCol As Long
LstCol = Cells(1, Columns.Count).End(xlToLeft)
Cells(1, LstCol + 1).Select
End Sub

Any of these ways should work fine for what you asked.
Hope it helps.

 

[Jenya]

Hi, Thanks for you help!!!! Much appreciated!!

The first ways work perfect. When I try to run the 3rd one it gives an error and it points to LstCol = Cells(1, Columns.Count).End(xlToLeft)...

 

[HalfAce]

Oh... my bad.
That offending line should read

LstCol = Cells(1, Columns.Count).End(xlToLeft)[COLOR=#ff0000].Column[/COLOR]

Sorry about that.

 

[Jenya]

It is OK. I really appreciate your help. I have managed to label those columns even.

I wonder if you could help me with another thing. I am trying to record another step of the same macro.

Basically I want to do auto-filter with multiple criteria but I don't want to hard code the column number i.e. The field number. In my Macro I want to remove the rows which contain "runrate" in column name Opportunity Name. Below is what I am trying to do..I am thinking there might be a way of combining those two things so it gives me dynamic field number...

Sub FindColumnOppName()
'
' Find Column Opportunity Name and filter runrate. Delete the results.

'Find the column name Opportunity name

Rows("1:1").Select
Selection.Find(What:="Opportunity Name", After:=ActiveCell, LookIn:= _
xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:= _
xlNext, MatchCase:=False, SearchFormat:=False).Activate

'Filter Column Opportunity name on runrate and run-rate

ActiveCell.Select
Selection.AutoFilter Field:=6, Criteria1:=Array( _
" runrate", " run-rate"), Operator:=xlFilterValues



End Sub

THANK YOU IN ADVANCE!