Finding The Last Column With Filled with Data in a Range

Question

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

Hi Tony, Thanks for comment I fix the mistake you said but still not working — Behseini
– Behseini, Commented Dec 28, 2013 at 9:37

L42 · Accepted Answer · 2013-12-28 09:54:35Z

4

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.

edited Dec 28, 2013 at 9:54

answered Dec 28, 2013 at 9:23

L42

19.8k11 gold badges47 silver badges70 bronze badges

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

5 Comments

Behseini Over a year ago

Thanks L42, but seems there is still something missing! I am not getting any errors but after running the code nothing change!

L42 Over a year ago

What row do you want to get the last column and what do you want your code to accomplish? You want to change color of what range?

Behseini Over a year ago

I was thinking to change the color of wole used range!

Behseini Over a year ago

I mean already used something like this With .Range("A" & Header & ":C" & LastRow) .Interior.ColorIndex = 16 End With

Behseini Over a year ago

Great , thanks it works now, what a big difference! I still do not know why you chosed Cells(1,5) why not Cells(1,1)? was there any reason?

Collectives™ on Stack Overflow

Finding The Last Column With Filled with Data in a Range

1 Answer 1

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related