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.

 

[erik.van.geit]

you can use the macro recorder to get the syntax
and there are VBA helpfiles of course

"2" is not a number, but a string
"Cells" syntax needs two numbers
Cells(number, number)

 

[ronnie76]

I tried removing the quotes from the 2 but VBA keeps giving me a
Compile error
Invalid or unqualified reference

then highlights .Columns

 

[erik.van.geit]

when you have a DOT that way, it should be within
WITH somesheet (or somerange)

END WITH

see the examples

 

[ronnie76]

Erik

Sub MatthewTest()
With ActiveSheet
Set currentcell = Cells(2, .Columns.Count).End(xlToRight)
If IsEmpty(currentcell) Then
'do nothing
Else
currentcell.Offset(1, 0).Select
End If
End With
End Sub

This code compiles but doesn't seem to work. Only your code works but I understood the other one a little better, so I was trying to adapt it. I do not understand the

xlValues, xlPart, xlByColumns, xlPrevious, False, False

part of your code, but I will research and see if I can get my brain around those commands.
Yours works great, I just want to be able to understand, that way I can modify it as the need arisies. Thank you again for all of your help.

 

[erik.van.geit]

editing my code is easy
do only change the line
WITH ......

to find out more about the arguments, see the VBA helpfiles for
FIND, method

best regards,
Erik

 

[matthew230]

If you are looking for easy to read code, then this will do what you want

Sub MatthewTest()

For Each currentcell In Range("2:2")
If IsEmpty(currentcell) = False Then
'do nothing
Else
currentcell.Select

Exit Sub

End If

Next

End Sub

 

[ronnie76]

Hi Matthew,

Thanks for your help. I think I understand your code, but I still have the problem that it always selects the last cell that does not have a formula in it. I want it to select the last cell that has no data or "value". My sheet is below, there are formulas in row 2 all the way to column N. When I run your code it selects cell M2, I need it to select I2.
Thanks again for all of the help.

Book2.xls
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	0	1	2	3	4	5	6	7	8	9	10	11	12
2	10	9	8	8	7	7	7	6
3
4
5
6
7	10	9	8	8	7	7	7	6
8
9
Sheet1

 

[matthew230]

Try this

Sub MatthewTest()

For Each currentcell In Range("2:2")
If currentcell.Value = "" Then
currentcell.Select

Exit Sub
Else


End If

Next

End Sub

 

[VoG]

I'm struggling to understand what you want. Is it the last cell in a row containing a value or a formula or what?

 

[ronnie76]

Matthew,
Yup that worked!

VoG II
Need to select last cell with a "value" not just formulas. Cell I2 has a formula but no value, that's the one I would need to select.

All,
Sorry it has been so difficult to help me understand. You guys have been a tremendous help though and I truly cannot thank you enough.
I am currently trying to get my company to pay to send me to a begginers Visual Basic class, I'm hoping that will help me understand VBA code a little better.

Thanks again guys,