Go To command for end of a column?

[kmatuszek]

A report groups certain account codes by default. We have a macro
(mostly built) to change this information. What we need to do is insert
a Go To or some other command that will look for the end of Column C
and not go to the end of the cells. Column C is dynamic depending on
the information placed in the cells. Comments?

 

[Harlan Grove]

A report groups certain account codes by default. We have a macro
(mostly built) to change this information. What we need to do is insert
a Go To or some other command that will look for the end of Column C
and not go to the end of the cells. Column C is dynamic depending on
the information placed in the cells. Comments?

worksheetobject.Cells(worksheetobject.Rows.Count, 3).End(xlUp)

or

worksheetobject.Cells(worksheetobject.Rows.Count, 3).End(xlUp).Offset(1, 0)

depending on whether you want the bottommost nonblank cell or the blank cell
immediately below the bottommost nonblank cell, respectively.

 

[Ron de Bruin]

Hi kmatuszek

This will select the last cell with a value

Sub test()
With ActiveSheet
..Cells(.Rows.Count, "C").End(xlUp).Select
End With
End Sub

This will select the row below the last cell with a value

Sub test2()
With ActiveSheet
..Cells(.Rows.Count, "C").End(xlUp)(2).Select
End With
End Sub

 

[kmatuszek]

Thank you both. I have printed out your suggestions and will give the
both a try! I appreciate your replying to my request as I have bee
pulling out my hair

 

[kmatuszek]

OK, got this far with the macro:

' Application.Goto Reference:="R9C1"
Range("A9").Select

'With ActiveSheet
Cells(Rows.Count, "C").End(xlUp)(4).Select

Range("A9:C103").Select
Range("C103").Activate
Selection.NumberFormat = "General"
Selection.SpecialCells(xlCellTypeBlanks).Select
Selection.FormulaR1C1 = "=R[-1]C"
End Sub

Now as you can see, I can begin at a specific cell (A9), it does go
down to the last column (with 3 spaces added). Now when I tell it to
select the whole range of cells it consistently chooses the same cells.
I need the select (as in select all Cells above the last cell in Column
C back up to A9) to be dynamic so that if the last cell is C125 it
doesn't keep selecting just C103.

Is there some way of coding it to select a range depending on a dynamic
number returned in code in 'the above line'?

Now I am going more gray with what hair I have left.

Thanks all for your help!

 

[Ron de Bruin]

If I understand you correct

Dim Lrow As Long
Lrow = Cells(Rows.Count, "C").End(xlUp)(4).Row
Range(Cells(9, 1), Cells(Lrow, 3)).Select

 

[Ron de Bruin]

Without selecting

Sub test()
Dim Lrow As Long
Lrow = Cells(Rows.Count, "C").End(xlUp)(4).Row
With Range(Cells(9, 1), Cells(Lrow, 3))
.NumberFormat = "General"
.SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=R[-1]C"
End With
End Sub

 

[kmatuszek]

RON YOU ARE A GENIUS!!!!
That worked perfectly - the small one is the one I tried. I le
everyone know I did not come up with the code but you did. I am no
taking credit for something I don't have a lot of experience for.
have to know what our 'surface' users might use, but when it comes t
coding - that is where it gets complicated for me.

THANK YOU again and again