Finding The Last Column With Filled with Data in a Range

1
votes

I know we can get the last row with data with following code:

LastRow = .Range("D" & .Rows.Count).End(xlUp).Row

But I am having trouble on getting the last Column with data. Here is what i tried bu as you can see from the image it didn't go through

Set ws = ThisWorkbook.ActiveSheet
With ws 
 Header = 5
 LastRow = .Range("D" & .Rows.Count).End(xlUp).Row
 LastCol = .Range(5 & .ColumnCount).End(xlLeft).Column
    With .Range("A" & Header & LastCol & LastRow)
         .Interior.ColorIndex = 16
    End With
End With

enter image description here

Can you please let me know hoe I can fix this? thanks

1
vbaexcel
Use Cells instead of Range. – L42
Use Columns.Count instead of Column.Count – Tony Dallimore
Hi Tony, Thanks for comment I fix the mistake you said but still not working – Behseini

1 Answers

3
votes

Try this as i've commented:

Lastcol = .Cells(5, Columns.Count).End(xlToLeft).Column

i'm not sure if its xlLeft or xlToLeft. Try it yourself.

Use this to color the entire range:

With .Range(Cells(1,5),Cells(Lastrow,Lastcol)
    .Interior.ColorIndex = 16
End With

this colors A5 to your last column and row.