Solved: the last cell in each column

[sakura.saki]

Hi guys,does anyone know how to find the last row in a certain column with no content? what I mean "with no content" here is: even there was something but later deleted. for example the last cell without content in column A?

[CodeNinja]

sheet1.range("A65536").end(xlup).row

This will find the last row WITH data in column a

[CatDaddy]

sheets(1).Range("A" & Rows.Count).End(xlUp).Row

for sheets longer than 65536 rows

[sakura.saki]

Hi Ninja, first thanks a lot for your reply. but I tried it, I don´t think it really gives the row with an empty cell, becaus on a sheet in column a, the first empty cell is in row 67, but somehow this code returns a far more later rows,like over 100...I may misused it. so what if I want to write something like find the first empty row from column "a" to "z"?

I have the following: For j=1 to 27
Set wrdTable = wrdDoc.Tables.Add(Range:=wrdRange, NumRows:=Sheets("excelReady").Range("A65536").Offset(0, j - 1).End(xlUp).Row, NumColumns:=1)

so how to express it so that it will find the first empty cell in column A and return the number of that row and do it for column B and so on?

[sakura.saki]

thanks! it is very thoughtful, but my level is not yet there, I didn´t even know that an excel sheet could be so long...

[CatDaddy]

never mind...

[CodeNinja]

Hi Ninja, first thanks a lot for your reply. but I tried it, I don´t think it really gives the row with an empty cell, becaus on a sheet in column a, the first empty cell is in row 67, but somehow this code returns a far more later rows,like over 100...I may misused it. so what if I want to write something like find the first empty row from column "a" to "z"?

first EMPTY cell would be sheet1.range("A1").end(xldown).row + 1
so... try putting data in column 1 of a blank spreadsheet... then run the following test scripts to see exactly what they do.


Sub test()
'find first empty cell in column A
MsgBox (Sheet1.Range("A1").End(xlDown).Row) + 1

End Sub

Sub test2()
'find first empty row AFTER ALL DATA in column A
MsgBox (Sheet1.Range("A65536").End(xlUp).Row + 1)

End Sub

[sakura.saki]

Hi, thanks for your reply. but I couldn´t change j-1 to j, b/c I want to find the empty cell in column a as well, and I also changed it to j as you suggested, it still doens´t work as expected.

[CatDaddy]

using xlDown is not as reliable for finding the last row as xlUp

[CodeNinja]

using xlDown is not as reliable for finding the last row as xlUp


XlDown will find the first empty cell... not sure what sakura.saki really wants... both have uses imho.

[sakura.saki]

you are a hero...I just found out everything you provide is right, just I made a very stupid mistake... thanks a lot for your patience! I would have gone mad if I have to talk to myself to figure out what i want...:thumb

[CodeNinja]

NumRows:=Sheets("excelReady").Range("A65536").Offset(0, j - 1).End(xlUp).Row


You need something more like this:


' add this function outside of the subroutine
Private Function getColLtr2(ByVal i As Integer) As String
getColLtr2 = Split(Cells(1, i).Address, "$")(1)
End Function

'change the following:
NumRows:=Sheets("excelReady").Range("A65536").Offset(0, j - 1).End(xlUp).Row
To:
NumRows:= sheets("ExcelRead").range(getColLtr(j) & "65536").end(xlup).row




Good luck...

[CatDaddy]

what was the problem sakura.saki?

[mikerickson]

If your column has formulas that could evaluate to "" (like =IF(B1=1,"","cat")), this will find the last row in that column that shows a value.

With Range("A:A")
MsgBox .Find("?", after:=.Cells(1, 1), LookIn:=xlValues, lookat:=xlWhole, searchdirection:=xlPrevious).Row
End With

The End(xlup) approach will find the last cell with a formula in it.

[sakura.saki]

now I don´t have the problem anymore...thanks to you guys!it is solved

[sakura.saki]

Hi mikerickson, thanks. but I don´t really know what will appear at the last row, so it doen´t really apply. but it doens´t matter, it is already solved.
:-)

[sakura.saki]

Hi Ninja, I don´t know how to thank you. it is really very nice of you. you are really an expert, that is why it might be hard for you to understand what kind of stupid mistake I made. the one I used to say that it doens´t work works actually fine. many many thanks! :bow: