Go To command for end of a column?

  • Thread starter Thread starter kmatuszek
  • Start date Start date
K

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?
 
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?
Click to expand...

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.
 
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
 
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
 
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!
 
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
 
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
 
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
 
Back
Top