Go to - special

[user21136]

Hi,

Using the "Go To" under "Edit", how do you find the first blank cell following numbers in a column please. I'm trying to record my first macro.

ie

Column C
12
34
56
68
<----This cell

Cheers Glenn

 

[Todd Bardoni]

Here's some code that will add a total of column C below your last row of data.

Sub Test() 
Dim myrng as Long 
Set i = Sheets(ActiveSheet.Name).UsedRange.Rows 
myrng = i.Count 
Range("C65536").End(xlUp).Offset(1, 0) = Application.WorksheetFunction.Sum(Range("C2:C" & myrng)) 
End Sub

Assumes you have a column header in Row 1.

 

[Richie(UK)]

Hi Glenn,

Although you can, using the Special option, use GoTo to find the last cell you will need a slightly different approach for what you want to do.

See if this helps:

Sub test()
    Dim rngNext As Range
    With Sheet1
        If .Range("C1").Value = "" Then
            Set rngNext = .Range("C1")
        Else
            Set rngNext = .Cells(Rows.Count, "C").End(xlUp).Offset(1, 0)
        End If
    End With
    MsgBox "Next free cell in column C is " & rngNext.Address
End Sub

HTH

 

[Smitty]

Heya Glenn,

This is "one" way:

Range("C1").End(xlDown).Select
ActiveCell.Offset(1, 0).Select

You can also start from the bottom and work your way up if you have spaces between your data.

Lookup "Offset" in the XL VBA Help File. It'll yield a good bit of information, as will a board search for "Offset" and "Last Row".

Good luck with your first macro , but note that the recorder will give you a ton of unnecessary code that will need/should be trimmed when you're done. Post it when you're ready and the whizzes here (with whom I am not to be mistaken! :wink: ) will make it look like a Hippy who just joined the Marines in no time!

Hope that helps,

Smitty

 

[NateO]

I tend to like:

[c65536].end(3)(2)

Edit: Smitty, tsk-tsk. Please don't tell this innocent individual to select anything!

xldown, is useful, but xlup is more dynamic with the specific question, they serve different purposes.

 

[Todd Bardoni]

What the heck is: end(3)(2)

I'll venture a guess and say that (3) is the column index but what is (2)???

 

[Smitty]

Sorry Nate

Working on keeping those nasty "Selects" outta my stuff! (I actually meant to mention that, but didn't have enough info...)

Note to OP: IGNORE ME! & Listen to Nate! ray:

Smitty

 

[user21136]

Woooow!!!!

Thanks for all the replies.

I'll try them out for sure but I wanted to see if I could record something using the "Go To" so I could learn for future things I may want to do without asking for the code all the time.


tbardoni,

Re: "Here's some code that will add a total of column C below your last row of data."

I was actually wanting to find the cell as I want to copy a cell from another sheet there. Although, that will be handy too, thanks!

Cheers Glenn

 

[Richie(UK)]

A quick translation for those who don't speak NateO :wink:

Sub NateO()

MsgBox [c65536].End(3).Item(2).Address

'End can be one of 4 directions; Left,Right,Up,Down : So 3=Up

'Item is used to offset from the specified range, uses rows if only one argument
' Row 1 is the row of the range itself so 2 is down 1 row

End Sub

PS. Nate - couldn't get Item to work without actually adding "Item" - is this because End was used first?

 

[Smitty]

Best advice is to record away and when you've got a question post back!

Smitty