If column A completely empty, one action; if not, another action?

[StargateFanFromWork]

Good Morning!

How can I modify a macro to do either of these 2 things below.

If column A is empty but for the header, the cursor is to go to the top,
which is done by this action:
Application.Goto Reference:="R1C1"
Range("A2").Select

If, on the other hand, there are items in any row of column A, then this
happens (i.e., cursor goes to the first empty row down in column A):
Application.Goto Reference:="R1C1"
Range("A2").Select
Selection.End(xlDown).Select
ActiveCell.Offset(1, 0).Select

I do have a macro that I'd been using up till now but that has an error when
I use a brand new copy of the sheet. This is the macro to be modified.
*********************************************************
Sub AddNewEntry()
'
ActiveSheet.Unprotect 'place at the beginning of the code

Application.Goto Reference:="R1C1"
Range("A2").Select
Selection.End(xlDown).Select
ActiveCell.Offset(1, 0).Select

ActiveSheet.Protect ' place at end of code
End Sub
*********************************************************

Thanks.

 

[Guest]

You accomplish this by setting the last row on Column 1.
ie.:
iLastRow=Range("A65536").End(Xlup).row

If iLastRow<>1
Range("A" & iLastRow).select
Else
Range("A1").select
End IF

 

[StargateFanFromWork]

This looks very promising!

I modified the code I had to this code below using your recommendation.
*********************************************************
Sub AddNewEntry()
'
ActiveSheet.Unprotect 'place at the beginning of the code

iLastRow = Range("A65536").End(xlUp).Row

If iLastRow<>1
Range("A" & iLastRow).Select
Else
Range("A2").Select
End If

ActiveSheet.Protect ' place at end of code
End Sub
*********************************************************
However, the row "If iLastRow<>1" shows up in red though I don't get any
error when I invoke the macro (odd) and though the cursor went perfectly to
the first empty row in column A if there were any entries in any of the
rows, when there was only the header, the cursor did not move up to A2. I'm
wondering if that red text means anything ...

So it works right half the time! <g> Do you have any suggestions on how to
fix this? Perhaps I just didn't didn't interpret the code correctly.

Oh, forgot to mention that I have XL2K.

Thanks! D

 

[Guest]

Im sorry I forgot the then statement
it should read something like this:

If iLastRow <> 1 Then
Range("A" & iLastRow).Select
Else
Range("A1").Select
End If

 

[StargateFanFromWork]

Yippy, one step closer.

No more red now <g>. Works great when there is something in rows, but still
the cursor doesn't move in empty sheet. If my cursor is in cell, say, B14,
after clicking button with macro, cursor still there. Funny though that if
there _is_ info in column A, the cursor will travel to first empty row in A
no matter where it starts out from, but when empty it doesn't work. Even I
know it should. What could be going on here, any idea??

D

 

[Guest]

There must be another instruction interrumpting the selection of A1. Try
stepping it through and you will see if it selects A1 or not, then add the
cell selection where required.
To step it through go into the visual basic editor and hit F8.

 

[StargateFanFromWork]

I'm sorry, I did something wrong. When I went and re-assigned the macro,
that seemed to make it work better. I honestly don't know what I did there.

Anyway, pls ignore my most previous message besides this. Something is
still not working right.

Pls note that whenever I post here, I've previously also tried to find
answers in the archives. I keep coming back here since I can't figure out
further problems on my own. At any rate, "iLastRow" gives me a compile
error, variable not defined (the macro is below). I don't know if the else
statement works now as I can't get past the first condition.

<sigh> Am I doing something wrong?

Thanks. D

Im sorry I forgot the then statement
it should read something like this:

If iLastRow <> 1 Then
Range("A" & iLastRow).Select
Else
Range("A1").Select
End If

Sub AddNewEntry()
'
ActiveSheet.Unprotect 'place at the beginning of the code

If iLastRow <> 1 Then
Range("A" & iLastRow).Select
Else
Range("A1").Select
End If

ActiveSheet.Protect ' place at end of code
End Sub

 

[Guest]

You probably have a statement in the global section where you state:

Option Explicit.

So you have to add the following statement right after the name of your macro:

Dim iLastRow as integer

That will take care of the "variable not defined" error

 

[StargateFanFromWork]

There must be another instruction interrumpting the selection of A1. Try
stepping it through and you will see if it selects A1 or not, then add the
cell selection where required.
To step it through go into the visual basic editor and hit F8.

Thank you for this tip. I knew there was a way to do that, from hints in
other posts, but never actually knew how.

I messed up somewhere, something I don't usually do (at least not something
like this). Anyway, I re-assigned the macro and checked this code with F8
and it stops right at "iLastRow" and gives me the compile error, variable
not defined.

Thanks! D

 

[StargateFanFromWork]

Will, this is just so much fun <g>.

Now I get this error:

Run-time error '1004'
Method 'Range' of object'_Global' failed

(?)

You're right in that I have option explicit at the top. I modifed code to
this. Perhaps I've done something wrong.
*********************************************************
Sub AddNewEntry()
'
ActiveSheet.Unprotect 'place at the beginning of the code

Dim iLastRow As Integer
If iLastRow <> 1 Then
Range("A" & iLastRow).Select
Else
Range("A1").Select
End If

ActiveSheet.Protect ' place at end of code
End Sub
*********************************************************

D

You probably have a statement in the global section where you state:

Option Explicit.

So you have to add the following statement right after the name of your macro:

Dim iLastRow as integer

That will take care of the "variable not defined" error

recommendation.

[snip]

 

[Guest]

You forgot the following piece:

iLastRow = Range("A65536").End(xlUp).Row

This line must go before the if statement! something like this:
Sub AddNewEntry()
'
Dim iLastRow As Integer

ActiveSheet.Unprotect 'place at the beginning of the code

iLastRow = Range("A65536").End(xlUp).Row

If iLastRow <> 1 Then
Range("A" & iLastRow).Select
Else
Range("A1").Select
End If

ActiveSheet.Protect ' place at end of code
End Sub

This should work

Will, this is just so much fun <g>.

Now I get this error:

Run-time error '1004'
Method 'Range' of object'_Global' failed

(?)

You're right in that I have option explicit at the top. I modifed code to
this. Perhaps I've done something wrong.
*********************************************************
Sub AddNewEntry()
'
ActiveSheet.Unprotect 'place at the beginning of the code

Dim iLastRow As Integer
If iLastRow <> 1 Then
Range("A" & iLastRow).Select
Else
Range("A1").Select
End If

ActiveSheet.Protect ' place at end of code
End Sub
*********************************************************

D

You probably have a statement in the global section where you state:

Option Explicit.

So you have to add the following statement right after the name of your macro:

Dim iLastRow as integer

That will take care of the "variable not defined" error

recommendation.

[snip]

 

[StargateFanFromWork]

Lord, thank you!! I don't know what it is, but I was in stupid mode today,
it seems. Thank you, this works lovely now. I believe that it'll be easier
for me in future as I now have a working model. I understand the concept of
IF THEN because I've used in before in other scripting languages, but for
some reason, in VB it eludes me. I guess because VB is the most obscure
scripting language I've ever tackled. All this time later and I'm still
struggling <lol>.

Thank you! D

You forgot the following piece:

iLastRow = Range("A65536").End(xlUp).Row

This line must go before the if statement! something like this:
Sub AddNewEntry()
'
Dim iLastRow As Integer

ActiveSheet.Unprotect 'place at the beginning of the code

iLastRow = Range("A65536").End(xlUp).Row

If iLastRow <> 1 Then
Range("A" & iLastRow).Select
Else
Range("A1").Select
End If

ActiveSheet.Protect ' place at end of code
End Sub

This should work

Will, this is just so much fun <g>.

Now I get this error:

Run-time error '1004'
Method 'Range' of object'_Global' failed

(?)

You're right in that I have option explicit at the top. I modifed code to
this. Perhaps I've done something wrong.
*********************************************************
Sub AddNewEntry()
'
ActiveSheet.Unprotect 'place at the beginning of the code

Dim iLastRow As Integer
If iLastRow <> 1 Then
Range("A" & iLastRow).Select
Else
Range("A1").Select
End If

ActiveSheet.Protect ' place at end of code
End Sub
*********************************************************

D

You probably have a statement in the global section where you state:

Option Explicit.

So you have to add the following statement right after the name of

your
macro:

Dim iLastRow as integer

That will take care of the "variable not defined" error

:

I'm sorry, I did something wrong. When I went and re-assigned the macro,
that seemed to make it work better. I honestly don't know what I

did
there.

Anyway, pls ignore my most previous message besides this. Something is
still not working right.

Pls note that whenever I post here, I've previously also tried to find
answers in the archives. I keep coming back here since I can't

figure
out

further problems on my own. At any rate, "iLastRow" gives me a compile
error, variable not defined (the macro is below). I don't know if

the
else

statement works now as I can't get past the first condition.

<sigh> Am I doing something wrong?

Thanks. D


Im sorry I forgot the then statement
it should read something like this:

If iLastRow <> 1 Then
Range("A" & iLastRow).Select
Else
Range("A1").Select
End If



:

This looks very promising!

I modified the code I had to this code below using your

recommendation.

[snip]