How to find out last cell in the column

[Shailender]

Hello All, Here is the question, I have the data till AW column, in that there are many blank cells in the columns, how to select AW19 cell through vba. I have highlighted the AW19 cell with red color.

Please refer to the Sheet1.

Thank you in advance!

 

[vletm]

In that case You maybe would like to get this:

aw_y_max = Sheets("Sheet1").UsedRange.Rows.Count

 

[Derek McGill]

In that case You maybe would like to get this:

aw_y_max = Sheets("Sheet1").UsedRange.Rows.Count

Use it like this :-

Sub AW()

aw_y_max = Sheets("Sheet1").UsedRange.Rows.Count

With Worksheets("Sheet1")
        .Cells.Item(aw_y_max, "AW").Interior.ColorIndex = 3
End With

End Sub

 

[vletm]

@Derek McGill - How to find ... means?

 

[Shailender]

Thank you Derek & Vletm for the reply, Could you please let me know how to select the last cell which was used in the sheet..? In this case i want to select AW19. Please refer to the above attached sheet.

 

[vletm]

with Sheets("Sheet1")
    y = .UsedRange.Rows.Count
    .range("AW"&y).select
end with

 

[Shailender]

@vletm, thank you for the prompt response, Every month the column will get increased. So how to find out the last used column dynamically..?

 

[vletm]

https://www.thespreadsheetguru.com/...to-find-the-last-row-or-last-column-using-vba

 

[Monty]

Try using the code after you active the sheet:

Dim J as integer
J = ActiveSheet.UsedRange.SpecialCells(xlCellTypeLastCell).row

 

[Monty]

Try this to find fist row last column dynamically.

Sub Test()

Dim lastColumn As Integer
With ActiveSheet
lastColumn = .Cells(1, .Columns.Count).End(xlToLeft).Column
End With
MsgBox lastColumn
End Sub

 

[Shailender]

@Monty the code is working. Thank you for your help!

 

[Monty]

My pleasure...