macro to find the last blank cell in col. A

P

peyman

hi guys,
how can I write a Macro to set the Activecell in the activesheet on the last
blank cell in column A?
thanks in advance.
peiman
 
K

Kevin B

Assuming that column A has a contiguous block of data until the next blank
cell is reached, the following macro should do the trick:

Sub LastBlankCell()

Dim varVal As Variant
Dim l As Long

varVal = Range("A1").Value

Do Until varVal = ""
l = l + 1
varVal = Range("A1").Offset(l).Value
Loop

Range("A" & (l + 1)).Select

End Sub
 
J

JE McGimpsey

One way:

Did you mean last *blank* cell? One way:

Public Sub LastBlankInColumnA()
With ActiveSheet.Columns(1).Cells
If Application.CountA(.Cells) = .Count Then Exit Sub 'No blanks
With .Cells(.Rows.Count, 1)
If IsEmpty(.Value) Then
.Activate
ElseIf IsEmpty(.Offset(-1, 0).Value) Then
.Offset(-1, 0).Activate
Else
.End(xlUp).Offset(-1, 0).Activate
End If
End With
End With
End Sub
 
J

JE McGimpsey

This may in fact be what the OP wants, but it doesn't find the *last*
blank cell...
 
G

Gary''s Student

Just start from the bottom and work upwards:

Sub sellstblcell()
Dim i As Long
For i = Rows.Count To 1 Step -1
If IsEmpty(Cells(i, "A")) Then
Cells(i, "A").Select
Exit Sub
End If
Next
End Sub
 
J

Jim Thomlinson

To be absolutely correct the last blank Cell in Column A is probably A65536
(it is the last cell in A and it is probalby blank) but I figured that that
is not what the OP wanted... You are correct though it is all in how you read
the question.
 
P

peyman

that's great.thanx Kevin

Kevin B said:
Assuming that column A has a contiguous block of data until the next blank
cell is reached, the following macro should do the trick:

Sub LastBlankCell()

Dim varVal As Variant
Dim l As Long

varVal = Range("A1").Value

Do Until varVal = ""
l = l + 1
varVal = Range("A1").Offset(l).Value
Loop

Range("A" & (l + 1)).Select

End Sub
Click to expand...
 
J

JLatham

Well, you asked for the last blank cell in column A. Jim Thomlinson gave you
a quick way to get to the first blank cell after the last non-blank cell in
column A, I'll repeat that here:

Sub NextBlankAfterLastNonBlank()
Range("A" & Rows.Count).End(xlup).Offset(1,0).Select
End sub

and a minor variation of that gets you to the last cell with something in
it, which was also provided earlier?

Sub LastCellWithSomethingInIt()
Range("A" & Rows.Count).End(xlup).Select
End sub
 
P

peyman

thank you.can you let me know how the "Range("A" &
Rows.Count).End(xlup).Select" works?!!what's the function of End(xlup)??!!!
 
J

JLatham

Excel Help is your friend. .End is the end of a range of like-contents
cells, either empty or blank. It takes one of 4 pararameters: xlUp, xlDown,
xlToLeft, xlToRight. so by using the command we did, we said
Start looking at A65536 ("A" & Rows.Count) and look UP (xlUP) to find the
end of the range - the assumption being that A65536 is most likely empty, so
it finds the first non empty cell as it looks UP the sheet from the bottom.
The .Offset(1,0) says "AHA!! You found a non-empty cell, now tell me what row
the cell right below that one is in." which is the first empty cell after the
last used cell in a given column.
 
D

Dave Peterson

It's the equivalent of selecting A65536 (in xl97-xl2003) and then hitting the
End key (on the keyboard) then the uparrow and finally the down arrow
(.offset(1,0)).
 
P

peyman

thanx Dave.

Dave Peterson said:
It's the equivalent of selecting A65536 (in xl97-xl2003) and then hitting the
End key (on the keyboard) then the uparrow and finally the down arrow
(.offset(1,0)).
Click to expand...
 
P

peyman

thanks a million!!!!

JLatham said:
Excel Help is your friend. .End is the end of a range of like-contents
cells, either empty or blank. It takes one of 4 pararameters: xlUp, xlDown,
xlToLeft, xlToRight. so by using the command we did, we said
Start looking at A65536 ("A" & Rows.Count) and look UP (xlUP) to find the
end of the range - the assumption being that A65536 is most likely empty, so
it finds the first non empty cell as it looks UP the sheet from the bottom.
The .Offset(1,0) says "AHA!! You found a non-empty cell, now tell me what row
the cell right below that one is in." which is the first empty cell after the
last used cell in a given column.
Click to expand...
 
G

Gary''s Student

If you are using a version of Excel prior to 2007 and if cell A65536 is
empty, then A65536 is the correct cell to be Selected
 
Top