Getting to first blank row from middle of long list?

[MS Questionnairess]

A colleague of mine has spreadsheet of over 4,000 entries.

She's constantly autofiltering to fill in missing bits of info but it's
driving her crazy that she can't just go from whatever cell she is in the
middle of the rows down to the first blank row at the very bottom.

The 'Page Up' and 'Page Down' aren't exact and 'End' doesn't seem to do
anything.

It sounds like such a simple thing to do... but... um... perhaps I'm just
not searching for the right vocabulary words to find any hints or tips?

So... I come to you(se) all-knowing kindly peoples

Help?

grazie und merci.

 

[Don Guillett]

try ctrl+home keys

 

[JMB]

try playing with Ctrl+Home, Ctrl+End (last cell in usedrange which is not
always the last cell with data), Ctrl+Down Arrow, Ctrl+Up Arrow

the Ctrl+Arrow keys will take you to the top or bottom (or left or right) of
the table - but will stop in the middle if there are gaps. I usually click
on a column I know has *no gaps* and use Ctrl+Down Arrow to get to the bottom
of the table or I use a column that is *empty* and use Ctrl+Down Arrow to get
to the bottom of the spreadsheet (row 65536) then select the column I
actually need and use Ctrl+Up Arrow to get to the last cell.

you could also use a macro to get to the last cell w/data in it for whatever
column you are in - you could create a button on your toolbar and assign the
macro to it.


Sub LastRow()
Dim rngBottom As Range

Set rngBottom = Cells(Rows.Count, ActiveCell.Column)

If IsEmpty(rngBottom) Then
rngBottom.End(xlUp).Activate
Else: rngBottom.Activate
End If

End Sub

 

[MS Questionnairess]

Thank you Don and JMB

The problem with this scenario is that the Ctrl+End or Ctrl+Down Arrow is
that it takes my colleague right down all the 2000 blank (but
formula-formated) rows at the bottom. There are going to be empty cells
within rows. That's why she's constantly moving around the sheet; inputting
information as it becomes available.

I've never done a macro before... but, JMB, yours works!! Not exactly... but
pretty darned happy close enough hurray-ly.

My colleague is in awe of me and I'm just humbled.

Thank you, thank you, thank you!

Weeeeeeeee

 

[Pete_UK]

You said earlier that <End> doesn't seem to do anything. You need to
follow it with an arrow key, and the cursor will move in that
direction to the last contiguous cell with data in it. So if you are
in a column with data in it and press <End> once followed by <down-
arrow> this will take you to the bottom cell of contiguous data (i.e.
it will stop at the next blank cell).

Hope this helps.

Pete

 

[MS Questionnairess]

Well... that makes sense

And it works as well as the macro. And now my colleague is more in awe of me
and I'm feeling kinda dumb I couldn't figure out that part of the
instructions myself

Thanks for the perfect clarification, Pete_UK.

 

[Pete_UK]

You're welcome - it's something I've been using (in Quattro as well as
Excel) for about 15 years or so.

Pete

 

[MartinW]

And one more. You can select an entire column, tap F5, click Special,
check blanks and OK. All blanks will be highlighted with the first one
active, you can add data to that cell and hit enter to take you to
the next blank etc etc.

HTH
Martin