VBA code to select last cell in row with data

[ronnie76]

I have a row of formulas that reference other sheets in my workbook (i.e. Cell A4=Sheet2!A1, Cell B4=Sheet2!B1, etc). I need to have VBA find the last cell in that row with data. I tried "End(xlToRight).Column" but it goes all the way to the end because all of the cells have formulas. I need to find the last cell that is empty of data or maybe >0 would work. Sorry if this is confusing. Thanks for any and all help.

 

[VoG]

Last filled column in row 1

LastCol = Cells(1, Columns.Count).End(xlToLeft).Column

 

[matthew230]

Confused if you want rows or columns as you mention both. Try the below for rows

With ActiveSheet
Set currentcell = .Cells(.Rows.Count, "A").End(xlUp)
If IsEmpty(currentcell) Then
'do nothing
Else

currentcell.Offset(1, 0).Select

End If

End With

 

[erik.van.geit]

Hi, this works for me

Sub test()
Dim LC As Long
 
    With Sheets("sheet1").Rows(4)
    LC = .Find("*", .Cells(1, 1), xlValues, xlPart, xlByColumns, xlPrevious, False, False).Column
    End With
 
MsgBox "the last column with a value in row 4 is column " & LC, vbInformation, "REPORT"
End Sub

kind regards,
Erik

 

[VoG]

Confused if you want rows or columns as you mention both. Try the below for rows

With ActiveSheet
Set currentcell = .Cells(.Rows.Count, "A").End(xlUp)
If IsEmpty(currentcell) Then
'do nothing
Else

currentcell.Offset(1, 0).Select

End If

End With

This is quicker

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

 

[ronnie76]

VoG II

Works great!

Thanks to everyone for the help!

 

[erik.van.geit]

VoG II

Works great!

Thanks to everyone for the help!

I do not understand this...

Or I am missing a lot
Or you didn't explain your problem clearly
Or you didn't see that the code is checking row 1
Or ???

EXAMPLE

  A     B     C     D  E       F  G  H  I  
3 entry other hello    lastone             
4 entry other hello    lastone             
sheet1
[Table-It] version 09 by Erik Van Geit

RANGE FORMULA (1st cell)
A4:I4 =IF(A3< >"",A3,"")
[Table-It] version 09 by Erik Van Geit

What is the expected result?
Wasn't it 5, since 5 is the last column with data, while the formulas continue till the end of the row.
When you run the code (changed for row 4)

LC = Cells(4, Columns.Count).End(xlToLeft).Column

you will not get 5, but one of two possible values:

1. a higher value, which is the last column containing a formula
2. or - this is your case - when the entire row is filled, the result will be 1

At least that is the results I get.

You need "find" looking to only the values, hence the argument "xlValues" in the code I posted.

kind regards,
Erik

 

[ronnie76]

Sorry. Actually, Used Erik and Matthews code. VoG II's went to the end of the row of formulas while the others stopped when the values where empty. I actuall had an =IF(Sheet2!A1>0,Sheet2!A1,"") statement in the cells. But again I cannot thank you all enough for the help. The only thing I can think of is to give you credit in a comment in the code. That is if that is ok with you guys?

Thanks again,

 

[erik.van.geit]

you know, this kind off code is really not "ours"

 

[ronnie76]

One follow up question.

Why does
.Cells(.Rows.Count, "A").End(xlUp)
compile but
.Cells(.Columns.Count, "2").End(xlToRight)
does not compile

Also is there any reference material to help learn how to enter correct syntax? Or is it mostly learned from experience?

Thank you guys so much,