Find first empty row

[Ragnar Midtskogen]

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

[Bob Phillips]

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" <[email protected]> wrote in message
news:[email protected]...
> 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
>
>

[eternal_cat via OfficeKB.com]

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

[Jim May]

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" <[email protected]> wrote in message
news:[email protected]...
> 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
>

[Ragnar Midtskogen]

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" <[email protected]> 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" <[email protected]> wrote in message
> news:[email protected]...
>> 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
>>
>
>

[Ragnar Midtskogen]

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

[Ragnar Midtskogen]

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.

Ragnar

"Bob Phillips" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
>> 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
>>
>>
>
>

[Bob Phillips]

You shouldn't do. You will get an error 91 if the whole worksheet is blank
but I am at a loss as to what could cause error 13. What do you have on the
worksheet?

--
HTH

Bob Phillips

"Ragnar Midtskogen" <[email protected]> wrote in message
news:[email protected]...
> 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.
>
> Ragnar
>
> "Bob Phillips" <[email protected]> wrote in message
> news:[email protected]...
> > 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" <[email protected]> wrote in message
> > news:[email protected]...
> >> 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
> >>
> >>
> >
> >
>
>

[Ragnar Midtskogen]

Bob,

There is just text. The first row has column header text strings in the
first 10 to 12 columns.
I have typed in some short text strings in some of hte firs rows, for
testing purposes.
That is it, no formulas, no macros.
I had changed the font in column 1 to Marlett, but changing it back did not
make any difference.

Ragnar

"Bob Phillips" <[email protected]> wrote in message
news:[email protected]...
> You shouldn't do. You will get an error 91 if the whole worksheet is blank
> but I am at a loss as to what could cause error 13. What do you have on
> the
> worksheet?
>
> --
> HTH
>
> Bob Phillips
>
> "Ragnar Midtskogen" <[email protected]> wrote in message
> news:[email protected]...
>> 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.
>>
>> Ragnar
>>
>> "Bob Phillips" <[email protected]> wrote in message
>> news:[email protected]...
>> > 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" <[email protected]> wrote in message
>> > news:[email protected]...
>> >> 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
>> >>
>> >>
>> >
>> >
>>
>>
>
>

[Ragnar Midtskogen]

Bob,

Mystery solved, I had left out the .Row at the end, so the return value was
a string with the contents of the non-empty cell with the largest row
number.
With the correct syntax it returns the row number of that cell, a long.
I have tested it with character strings, including numbers, in various
columns, and it consistently gives the correct result.

Thank you Bob!

Ragnar

"Ragnar Midtskogen" <[email protected]> wrote in message
news:[email protected]...
> Bob,
>
> There is just text. The first row has column header text strings in the
> first 10 to 12 columns.
> I have typed in some short text strings in some of hte firs rows, for
> testing purposes.
> That is it, no formulas, no macros.
> I had changed the font in column 1 to Marlett, but changing it back did
> not make any difference.
>
> Ragnar
>
> "Bob Phillips" <[email protected]> wrote in message
> news:[email protected]...
>> You shouldn't do. You will get an error 91 if the whole worksheet is
>> blank
>> but I am at a loss as to what could cause error 13. What do you have on
>> the
>> worksheet?
>>
>> --
>> HTH
>>
>> Bob Phillips
>>
>> "Ragnar Midtskogen" <[email protected]> wrote in message
>> news:[email protected]...
>>> 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.
>>>
>>> Ragnar
>>>
>>> "Bob Phillips" <[email protected]> wrote in message
>>> news:[email protected]...
>>> > 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" <[email protected]> wrote in message
>>> > news:[email protected]...
>>> >> 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
>>> >>
>>> >>
>>> >
>>> >
>>>
>>>
>>
>>
>
>

[Bob Phillips]

I am glad you found that as I had no ideas to share with you :-).

--
HTH

Bob Phillips

"Ragnar Midtskogen" <[email protected]> wrote in message
news:[email protected]...
> Bob,
>
> Mystery solved, I had left out the .Row at the end, so the return value
was
> a string with the contents of the non-empty cell with the largest row
> number.
> With the correct syntax it returns the row number of that cell, a long.
> I have tested it with character strings, including numbers, in various
> columns, and it consistently gives the correct result.
>
> Thank you Bob!
>
> Ragnar
>
> "Ragnar Midtskogen" <[email protected]> wrote in message
> news:[email protected]...
> > Bob,
> >
> > There is just text. The first row has column header text strings in the
> > first 10 to 12 columns.
> > I have typed in some short text strings in some of hte firs rows, for
> > testing purposes.
> > That is it, no formulas, no macros.
> > I had changed the font in column 1 to Marlett, but changing it back did
> > not make any difference.
> >
> > Ragnar
> >
> > "Bob Phillips" <[email protected]> wrote in message
> > news:[email protected]...
> >> You shouldn't do. You will get an error 91 if the whole worksheet is
> >> blank
> >> but I am at a loss as to what could cause error 13. What do you have on
> >> the
> >> worksheet?
> >>
> >> --
> >> HTH
> >>
> >> Bob Phillips
> >>
> >> "Ragnar Midtskogen" <[email protected]> wrote in message
> >> news:[email protected]...
> >>> 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.
> >>>
> >>> Ragnar
> >>>
> >>> "Bob Phillips" <[email protected]> wrote in message
> >>> news:[email protected]...
> >>> > 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" <[email protected]> wrote in message
> >>> > news:[email protected]...
> >>> >> 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
> >>> >>
> >>> >>
> >>> >
> >>> >
> >>>
> >>>
> >>
> >>
> >
> >
>
>