Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find first empty row
Hello,
I need to find the first empty row in a worksheet. The sheet is just used to enter text in, has no formulas. I have Googled this and I am somewhat confused, there appears to be many definitions of what is empty and many ways to find the row. I have found something that seems to work, but I don't quite understand how it works iRow = objCurrentSheet.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0)Row First, it looks like it just looks at column 1. It looks like it starts at the last row and moves the top of the range up until it finds something in a cell, but I don't see how it determines when a cell contains something. I need to scan several columns, so even if this code works how would I expand the area scanned? I have something that scans entire rows, I could use that but would prefer to limit the scan to the first n columns, in case someone happened to enter something outside the matrix to be used. Any help would be appreciated. Ragnar |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find first empty row
Try this to find the last row over many columns
Function LastRow() As Long LastRow = Cells.Find(What:="*", _ After:=Range("A1"), _ SearchOrder:=xlByRows, _ SearchDirection:=xlPrevious).Row End Function -- HTH Bob Phillips "Ragnar Midtskogen" wrote in message ... Hello, I need to find the first empty row in a worksheet. The sheet is just used to enter text in, has no formulas. I have Googled this and I am somewhat confused, there appears to be many definitions of what is empty and many ways to find the row. I have found something that seems to work, but I don't quite understand how it works iRow = objCurrentSheet.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0)Row First, it looks like it just looks at column 1. It looks like it starts at the last row and moves the top of the range up until it finds something in a cell, but I don't see how it determines when a cell contains something. I need to scan several columns, so even if this code works how would I expand the area scanned? I have something that scans entire rows, I could use that but would prefer to limit the scan to the first n columns, in case someone happened to enter something outside the matrix to be used. Any help would be appreciated. Ragnar |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find first empty row
Thank you Bob,
Maybe I am missing something here, but when I try that I get runtime error 13, type mismatch. I tried deleting the wildcard character, then I get no error but it returns 0. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find first empty row
JUST TYPE ctrl-DOWN.
Ragnar Midtskogen wrote: Hello, I need to find the first empty row in a worksheet. The sheet is just used to enter text in, has no formulas. I have Googled this and I am somewhat confused, there appears to be many definitions of what is empty and many ways to find the row. I have found something that seems to work, but I don't quite understand how it works iRow = objCurrentSheet.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0)Row First, it looks like it just looks at column 1. It looks like it starts at the last row and moves the top of the range up until it finds something in a cell, but I don't see how it determines when a cell contains something. I need to scan several columns, so even if this code works how would I expand the area scanned? I have something that scans entire rows, I could use that but would prefer to limit the scan to the first n columns, in case someone happened to enter something outside the matrix to be used. Any help would be appreciated. Ragnar -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...mming/200510/1 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find first empty row
Thanks but I need to do this from VB.
Hitting Ctrl and Down Arrow is just the equivalent of this VBA code: Selection.End(xlDown).Select If I then hit Ctrl and Up Arrow it is the equivalent of this VBA code: Selection.End(xlUp).Select which does stop at the first non-empty row on the way up. Ragnar "eternal_cat via OfficeKB.com" <u14645@uwe wrote in message news:55a767697f90f@uwe... JUST TYPE ctrl-DOWN. Ragnar Midtskogen wrote: Hello, I need to find the first empty row in a worksheet. The sheet is just used to enter text in, has no formulas. I have Googled this and I am somewhat confused, there appears to be many definitions of what is empty and many ways to find the row. I have found something that seems to work, but I don't quite understand how it works iRow = objCurrentSheet.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0)Row First, it looks like it just looks at column 1. It looks like it starts at the last row and moves the top of the range up until it finds something in a cell, but I don't see how it determines when a cell contains something. I need to scan several columns, so even if this code works how would I expand the area scanned? I have something that scans entire rows, I could use that but would prefer to limit the scan to the first n columns, in case someone happened to enter something outside the matrix to be used. Any help would be appreciated. Ragnar -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...mming/200510/1 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find first empty row
Cells(Rows.Count, 1).End(xlUp).Offset(1, 0)Row
Here's how you read this statement,,, Cells - With the cells on your worksheet (Rows.Count, 1) - Visualize yourself clicking in the cell at row 65536 column 1 (or cell A65536) ..End(xlUp) Visualize yourself next pressing the End key and then the Up Arrow << keyboard equivalents The results of the above would cause your cursor to run up column A to the first non-blank cell and stop on or in it. ..Offset(1, 0) - means move down 1 row, move 0 columns ..row - not sure I just do it.... LOL HTH "Ragnar Midtskogen" wrote in message ... Hello, I need to find the first empty row in a worksheet. The sheet is just used to enter text in, has no formulas. I have Googled this and I am somewhat confused, there appears to be many definitions of what is empty and many ways to find the row. I have found something that seems to work, but I don't quite understand how it works iRow = objCurrentSheet.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0)Row First, it looks like it just looks at column 1. It looks like it starts at the last row and moves the top of the range up until it finds something in a cell, but I don't see how it determines when a cell contains something. I need to scan several columns, so even if this code works how would I expand the area scanned? I have something that scans entire rows, I could use that but would prefer to limit the scan to the first n columns, in case someone happened to enter something outside the matrix to be used. Any help would be appreciated. Ragnar |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find first empty row
Thank you,
I see what is going on now, but it works only for one column. I suppose I could repeat for all columns and check which column had the highest row number. Ragnar "Jim May" wrote in message news:BWD2f.3159$jw6.677@lakeread02... Cells(Rows.Count, 1).End(xlUp).Offset(1, 0)Row Here's how you read this statement,,, Cells - With the cells on your worksheet (Rows.Count, 1) - Visualize yourself clicking in the cell at row 65536 column 1 (or cell A65536) .End(xlUp) Visualize yourself next pressing the End key and then the Up Arrow << keyboard equivalents The results of the above would cause your cursor to run up column A to the first non-blank cell and stop on or in it. .Offset(1, 0) - means move down 1 row, move 0 columns .row - not sure I just do it.... LOL HTH "Ragnar Midtskogen" wrote in message ... Hello, I need to find the first empty row in a worksheet. The sheet is just used to enter text in, has no formulas. I have Googled this and I am somewhat confused, there appears to be many definitions of what is empty and many ways to find the row. I have found something that seems to work, but I don't quite understand how it works iRow = objCurrentSheet.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0)Row First, it looks like it just looks at column 1. It looks like it starts at the last row and moves the top of the range up until it finds something in a cell, but I don't see how it determines when a cell contains something. I need to scan several columns, so even if this code works how would I expand the area scanned? I have something that scans entire rows, I could use that but would prefer to limit the scan to the first n columns, in case someone happened to enter something outside the matrix to be used. Any help would be appreciated. Ragnar |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
To find row is empty or not | Excel Discussion (Misc queries) | |||
Find first empty row | Excel Discussion (Misc queries) | |||
Find Last Row or Empty Row in VBA | Excel Programming | |||
Find last empty row | Excel Programming | |||
Find Next Empty Row | Excel Programming |