Focus on next blank cell

[SangelNet]

Im trying to put together something that will set the focus on the
first blank cell of a range ("A") for each sheet.

the idea is that if i click on sheet1 the cursor will already be in
the first blank cell of column A. And if i click on sheets 2 and 3,
the same thing should happen.

Any ideas or links that tell me how to acomplish this?

Thnx

 

[CFS]

Use a Worksheet event:

Private Sub Worksheet_Activate()
Range("A65536").End(xlUp).Offset(1, 0).Select
End Sub

 

[Ronald R. Dodge, Jr.]

You can setup within the Activate event of the worksheet itself to use the
following code:

If ActiveSheet.Range("A1").Formula = "" Then
ActiveSheet.Range("A1").Select
Elseif ActiveSheet.Range("A2").Formula = "" Then
ActiveSheet.Range("A2").Select
Else
ActiveSheet.Range("A1").End(xlDown).Select
If Selection.Row < ActiveSheet.Rows.Count Then
Selection.Offset(1,0).Select
End If
End If

--
Thanks,

Ronald R. Dodge, Jr.
Production Statistician
Master MOUS 2000

 

[Ronald R. Dodge, Jr.]

That only works if there are no blank cells above the last cell with
something in it.

--
Thanks,

Ronald R. Dodge, Jr.
Production Statistician
Master MOUS 2000

 

[Rick Rothstein]

Try this worksheet event code...

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
Sh.Range("A1").End(xlDown).Offset(1).Activate
End Sub

To implement it, right click the icon immediately to the left of the File
menu item, select View Code from the popup menu that appears and then
copy/paste the above code into the code window that opened up. Now, go back
to your worksheet and select different sheets.

 

[SangelNet]

Use a Worksheet event:

Private Sub Worksheet_Activate()
    Range("A65536").End(xlUp).Offset(1, 0).Select
End Sub

thank you it worked. had to change the xlup for xldown.

thnx again.

 

[Ronald R. Dodge, Jr.]

Would work, but what if the user doesn't want to have it for all worksheets,
or either Cell A1 or Cell A2 is blank?

--
Thanks,

Ronald R. Dodge, Jr.
Production Statistician
Master MOUS 2000

 

[Rick Rothstein]

Would work, but what if the user doesn't want to have it for all

worksheets, or either Cell A1 or Cell A2 is blank?

I answered the question the OP asked. His first sentence was...

"I'm trying to put together something that will set the focus
on the first blank cell of a range ("A") for each sheet.

I assumed "first blank cell of a range ("A") meant the first blank cell in
Column A no matter where it occurred. His the last 3 words covers your other
question.

 

[Ronald R. Dodge, Jr.]

Yes, that's my point. If A1 is blank, and you do the End(xlDown) on A1, it
still won't be selecting A1 with A1 being the first blank cell.

If A1 is not blank, but A2 is blank, once again, when you use the
End(xlDown) on A1, it will still be below A2, so once again, the first blank
cell is not selected.

Also, if every single cell in A1 has something in it, it will error out as
it can't go to a cell of A65537.

--
Thanks,

Ronald R. Dodge, Jr.
Production Statistician
Master MOUS 2000

 

[Rick Rothstein]

Good point! Thanks for catching my momentary "lapse".

Here is new code (still for the Workbook SheetActivate event so it will
apply to all sheets) which should work as the OP wanted...

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
Dim LastCell As Range
Set LastCell = Sh.Cells(Sh.Rows.Count, "A").End(xlUp)
On Error GoTo Whoops
Sh.Range("A1:A" & LastCell.Row).Find("", After:=LastCell, _
SearchOrder:=xlByRows).Activate
Exit Sub
Whoops:
If LastCell.Row = 1 Then
Range("A1").Activate
Else
LastCell.Offset(1).Activate
End If
End Sub

 

[Ronald R. Dodge, Jr.]

That still doesn't work for the following 2 cases:

If every single cell in column A is filled as A1 will be selected

If only A1 has something in it, A1 will still be selected.

Also, even though Activate is valid to use, from what I have read, Select is
the preferred method for when selected either a single cell or a range.

--
Thanks,

Ronald R. Dodge, Jr.
Production Statistician
Master MOUS 2000

 

[Rick Rothstein]

I'm not sure what should be done if all of column A is filled and there are
no blank cells in it. Since the OP said he wanted the selection to be in
Column A, I guess selecting A1 is as good a solution as any for this
situation. Although this situation would come up quite rarely I would
assume; and, if A1 wasn't acceptable, code could easily be added to select
some other cell. I'm curious as to what cell you would select for it?

The second case you mentioned was an oversight in my code and easily
accounted for...

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
Dim LastCell As Range
Set LastCell = Sh.Cells(Sh.Rows.Count, "A").End(xlUp)
On Error GoTo Whoops
Sh.Range("A1:A" & LastCell.Row).Find("", After:=LastCell, _
SearchOrder:=xlByRows).Select
Exit Sub
Whoops:
If LastCell.Row = 1 And LastCell.Value = "" Then
Range("A1").Select
Else
LastCell.Offset(1).Select
End If
End Sub

For single cell selection, there doesn't seem to be any real difference
between using Select or Activate. Note that I changed the Activate Method
calls from my other posting to Select Method calls in the code above.

--
Rick (MVP - Excel)

That still doesn't work for the following 2 cases:

If every single cell in column A is filled as A1 will be selected

If only A1 has something in it, A1 will still be selected.

Also, even though Activate is valid to use, from what I have read, Select
is the preferred method for when selected either a single cell or a range.

--
Thanks,

Ronald R. Dodge, Jr.
Production Statistician
Master MOUS 2000

 

[SangelNet]

That still doesn't work for the following 2 cases:

If every single cell in column A is filled as A1 will be selected

If only A1 has something in it, A1 will still be selected.

Also, even though Activate is valid to use, from what I have read, Selectis
the preferred method for when selected either a single cell or a range.

--
Thanks,

Ronald R. Dodge, Jr.
Production Statistician

This last one does just what i need. thnx alot

you guys are great!!

 

[Rick Rothstein]

Actually, Ronald raised a good point with his last case... see my latest
code in this same sub-thread for code that handles that situation as well.

--
Rick (MVP - Excel)

That still doesn't work for the following 2 cases:

If every single cell in column A is filled as A1 will be selected

If only A1 has something in it, A1 will still be selected.

Also, even though Activate is valid to use, from what I have read, Select
is
the preferred method for when selected either a single cell or a range.

--
Thanks,

Ronald R. Dodge, Jr.
Production Statistician
Master MOUS 2000"Rick Rothstein" <[email protected]>
wrote in message

This last one does just what i need. thnx alot

you guys are great!!

 

[Ronald R. Dodge, Jr.]

Yep, understandable. In my example that I posted earlier, I used the
Formula property, so as to not only account for the previous situations, but
also to account for a cell value that may have an empty string value, but is
not truly empty. Of course, if the user is using that column strictly as
data entry, then your method would work as well.

Sorry if it seems like I get knick picking spotting so many different
things, but then when you get use to working with people that hardly even
know how to turn on a computer as I do and need to do programming work just
for that group of people to use, you get use to looking for anything that
may go wrong. Doesn't mean I catch them all, but still quite a few of them.
A few flat out reject having to use the system other than what the company
forces them to use. Some use only what they have to, and know what it's
basically for, but don't understand computers. Some understand computers,
but has no desire to learn much. Some has the desire to learn some, but not
into the technical stuff. A minor few has actually wanted to learn the
technical stuff for their own use and I have been able to help them learn
along the way.

In the case of all cells with something in it, I just have the very last
cell selected. Unless they really have that much data entry happening, the
only way I can see that happening is if they have something that pulls in so
much information from another source that it could very easily lead to
overflowing issues, which I have seen with query add-in programs linked into
Excel.

Yeah, I look for whatever could possibly go wrong and/or give an unexpected
results. I don't know about you, but I don't like to have to go back out
and touch every single computer, even if it is only deleting modules and
then importing modules.

--
Thanks,

Ronald R. Dodge, Jr.
Production Statistician
Master MOUS 2000