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

cells(rows.count, "A").end(xlup).Offset(1,0).Select
--
HTH...

Jim Thomlinson


"peyman" wrote:

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

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

--
Kevin Backmann


"peyman" wrote:

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

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

In article ,
peyman wrote:

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

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

In article ,
Jim Thomlinson wrote:

cells(rows.count, "A").end(xlup).Offset(1,0).Select

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

--
Gary''s Student - gsnu200745


"peyman" wrote:

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

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.
--
HTH...

Jim Thomlinson


"JE McGimpsey" wrote:

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

In article ,
Jim Thomlinson wrote:

cells(rows.count, "A").end(xlup).Offset(1,0).Select

how about finding the last non-blank cell in col A?
thanks

"Kevin B" wrote:

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

--
Kevin Backmann


"peyman" wrote:

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

that's great.thanx Kevin

"Kevin B" wrote:

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

--
Kevin Backmann


"peyman" wrote:

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

hi,
this macro takes the active cell to A65536!!!

"Gary''s Student" wrote:

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

--
Gary''s Student - gsnu200745


"peyman" wrote:

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

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 he

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


"peyman" wrote:

hi,
this macro takes the active cell to A65536!!!

"Gary''s Student" wrote:

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

--
Gary''s Student - gsnu200745


"peyman" wrote:

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

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

"JLatham" wrote:

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 he

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


"peyman" wrote:

hi,
this macro takes the active cell to A65536!!!

"Gary''s Student" wrote:

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

--
Gary''s Student - gsnu200745


"peyman" wrote:

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

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.

"peyman" wrote:

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

"JLatham" wrote:

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 he

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


"peyman" wrote:

hi,
this macro takes the active cell to A65536!!!

"Gary''s Student" wrote:

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

--
Gary''s Student - gsnu200745


"peyman" wrote:

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

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)).



peyman wrote:

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

"JLatham" wrote:

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 he

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


"peyman" wrote:

hi,
this macro takes the active cell to A65536!!!

"Gary''s Student" wrote:

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

--
Gary''s Student - gsnu200745


"peyman" wrote:

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

--

Dave Peterson

thanx Dave.

"Dave Peterson" wrote:

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)).



peyman wrote:

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

"JLatham" wrote:

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 he

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


"peyman" wrote:

hi,
this macro takes the active cell to A65536!!!

"Gary''s Student" wrote:

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

--
Gary''s Student - gsnu200745


"peyman" wrote:

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

--

Dave Peterson

thanks a million!!!!

"JLatham" wrote:

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.

"peyman" wrote:

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

"JLatham" wrote:

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 he

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


"peyman" wrote:

hi,
this macro takes the active cell to A65536!!!

"Gary''s Student" wrote:

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

--
Gary''s Student - gsnu200745


"peyman" wrote:

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

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
--
Gary''s Student - gsnu200745


"peyman" wrote:

hi,
this macro takes the active cell to A65536!!!

"Gary''s Student" wrote:

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

--
Gary''s Student - gsnu200745


"peyman" wrote:

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