PDA

View Full Version : Solved: the last cell in each column



sakura.saki
06-21-2012, 07:29 AM
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
06-21-2012, 08:01 AM
sheet1.range("A65536").end(xlup).row

This will find the last row WITH data in column a

CatDaddy
06-21-2012, 08:31 AM
sheets(1).Range("A" & Rows.Count).End(xlUp).Row

for sheets longer than 65536 rows

sakura.saki
06-21-2012, 08:34 AM
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
06-21-2012, 08:36 AM
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
06-21-2012, 08:39 AM
never mind...

CodeNinja
06-21-2012, 08:42 AM
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
06-21-2012, 08:43 AM
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
06-21-2012, 08:47 AM
using xlDown is not as reliable for finding the last row as xlUp

CodeNinja
06-21-2012, 08:52 AM
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
06-21-2012, 08:58 AM
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
06-21-2012, 09:05 AM
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
06-21-2012, 09:18 AM
what was the problem sakura.saki?

mikerickson
06-21-2012, 05:57 PM
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
06-22-2012, 02:28 AM
now I donīt have the problem anymore...thanks to you guys!it is solved

sakura.saki
06-22-2012, 02:30 AM
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
06-22-2012, 02:32 AM
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: