In Excel, determine first empty row & insert text 1

[clhare]

In Excel, can I use a macro to determine the first empty row in a spreadsheet (or even just first the empty column 1), then insert text into the first three columns of that row?

I'm not very familiar with Excel, so I'm a little lost on this one.

Any help is greatly appreciated!

Cheryl

 

[Loomah]

Hi Cheryl
There are two FAQs in this forum that will give you a method to determine the last row containing data. I'm not sure about xlbo's but mine also covers finding the last column containing data. In both cases all you will need to do is add 1 to the result.

The addidng 1 bit would apply to any method you use
eg
emptyrow = range("A1").end(xldown).row + 1
or
emptycol = range("IV1").end(xltoleft).column + 1
etc.

;-)

If a man says something and there are no women there to hear him, is he still wrong?
The faqs ma'am, just the faqs. Get the best from these forums : faq222-2244

 

[srogers]

Loomah -
Hope you don't mind but I have a question about your FAQ.

I want to use it to find where to paste my next row of data so I added a + 1 to the end of LastRow.

LastRow = ActiveSheet.Cells.Find(What:="*", _
SearchDirection:=xlPrevious, _
SearchOrder:=xlByRows).Row + 1

And now I'm ready to paste.
Would LastRow be the range I paste to?

I tried this:
ActiveSheet.Paste Destination:=Worksheets("A").Range("LastRow")

but I get application or object-defined error.

Then I tried this and got subscript out of range:
ThisWorkbook.Worksheets("A").Range(LastRow).PasteSpecial (xlValues)
(not knowing whether I need quotes or not)


Maybe I am supposed to: Dim LastRow as Range?

Thank you -

 

[PHV]

srogers, LastRow is a row number i.e. a Long, not a Range.
Try to use .Cells(LastRow, 1) instead of .Range

Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[srogers]

Hi PHV -
That DID help me. It works now.

Thanks so much.
Sharon

 

[Loomah]

Sharon
PHV has obviously given you the explanation you needed but I have amended my FAQ by declaring the variables properly and (hopefully) making it a bit easier to read. I've also added a little bit about getting the first empty row.
Happy Easter
;-)

If a man says something and there are no women there to hear him, is he still wrong?
The faqs ma'am, just the faqs. Get the best from these forums : faq222-2244

 

[srogers]

Loomah,
I just read it - that is great - thanks a bunch!
Sharon