Macro issues...

[Natalie]

I would like to create a macro which can be used on
several worksheets. I'm having to cut/paste info into
these worksheets from another worksheet. I need this
macro to highlight all cells and autosize them and then
(here's the problem I can't figure out...) go to the
first empty cell on column A. When I do a Cntr+End and
then down arrow and Home, it will keep that row's number
in the macro..In other words, it won't go to the first
empty cell in all the worksheets.This probably makes no
sense at all...but if you can understand what I'm trying
to say-Please help me!!!! Thanks all

 

[Harald Staff]

Sub test()
Dim R As Long
R = Columns("A:A").Find(What:="", LookAt:=xlWhole).Row
MsgBox "Row " & R, , "First blank cell:"
R = Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
MsgBox "Row " & R, , "First cell below last A cell:"
End Sub

 

[Natalie]

Thanks Harald-is there a way to actually highlight the
empty cell instead of a message box popping up and
telling where the next empty cell is?

 

[dizzykid]

Natalie,

When you say highlight you mean select (as in put the cursor there), right?
If so, put this after (or in place of) the messagebox line:
Cells(R,1).Select
Now if you were to paste data, it should be positioned correctly.
HTH,
Chris

 

[Natalie]

Worked like a charm-Thanks Chris!!!!!

-----Original Message-----
Natalie,

When you say highlight you mean select (as in put the cursor there), right?
If so, put this after (or in place of) the messagebox line:
Cells(R,1).Select
Now if you were to paste data, it should be positioned correctly.
HTH,
Chris




.