Simple questions (I hope)

[Guest]

1) In Column A:A I want to select A3: the last cell in that column with data
in it (non-empty cell).

2) On this code = [ If ActiveCell.Row <> 1 Then ] I want to expand it to
If ActiveCell.Row <> 1 or 2 Then. But the "or 2" is throwing things off???

 

[Nigel]

1. The following will return the last row number with data and susbstitue
into the select

Dim lstrow As Long
lstrow = Sheets("WSname").Cells(Rows.Count, "A").End(xlUp).Row
Range("A" & lstrow).Select

2. You need to be explicit about the second condition eg....

If ActiveCell.Row <> 1 or ActiveCell.Row <> 2 Then

Cheers
Nigel

1) In Column A:A I want to select A3: the last cell in that column with data
in it (non-empty cell).

2) On this code = [ If ActiveCell.Row <> 1 Then ] I want to expand it to
If ActiveCell.Row <> 1 or 2 Then. But the "or 2" is throwing things

off???

 

[Tom Ogilvy]

2. You need to be explicit about the second condition eg....

If ActiveCell.Row <> 1 or ActiveCell.Row <> 2 Then

While it is not clear what the OP means exactly by 1 or 2, I would suspect
he wants something like

If ActiveCell.Row <> 1 and ActiveCell.Row <> 2 then

I say this, because I would be hard pressed to come up with an activecell
that did not meet one of the conditions of not being row 1 or not being row
2 as stated using an OR connector.


If I am right, then a less explicit (but equally effective) approach might
be

if ActiveCell.Row > 2 then

of
If ActiveCell.row >= 3 then

--
Regards,
Tom Ogilvy

1. The following will return the last row number with data and susbstitue
into the select

Dim lstrow As Long
lstrow = Sheets("WSname").Cells(Rows.Count, "A").End(xlUp).Row
Range("A" & lstrow).Select

2. You need to be explicit about the second condition eg....

If ActiveCell.Row <> 1 or ActiveCell.Row <> 2 Then

Cheers
Nigel

1) In Column A:A I want to select A3: the last cell in that column with data
in it (non-empty cell).

2) On this code = [ If ActiveCell.Row <> 1 Then ] I want to expand it to
If ActiveCell.Row <> 1 or 2 Then. But the "or 2" is throwing things

off???

 

[DMoney]

First and second condition must be explicit. based on
your code, I believe what u are trying to achieve should
be done using the and operator -- meaning check this
condition and this condition.

Try this

If ActiveCell.Row <> 1 And ActiveCell.Row <> 2 Then

 

[Nigel]

Of course you are right Tom, I had not looked at the logic more the explicit
condition.

The OP will no doubt benefit.

Cheers
Nigel

2. You need to be explicit about the second condition eg....

If ActiveCell.Row <> 1 or ActiveCell.Row <> 2 Then

While it is not clear what the OP means exactly by 1 or 2, I would suspect
he wants something like

If ActiveCell.Row <> 1 and ActiveCell.Row <> 2 then

I say this, because I would be hard pressed to come up with an activecell
that did not meet one of the conditions of not being row 1 or not being row
2 as stated using an OR connector.


If I am right, then a less explicit (but equally effective) approach might
be

if ActiveCell.Row > 2 then

of
If ActiveCell.row >= 3 then

--
Regards,
Tom Ogilvy

1. The following will return the last row number with data and susbstitue
into the select

Dim lstrow As Long
lstrow = Sheets("WSname").Cells(Rows.Count, "A").End(xlUp).Row
Range("A" & lstrow).Select

2. You need to be explicit about the second condition eg....

If ActiveCell.Row <> 1 or ActiveCell.Row <> 2 Then

Cheers
Nigel

1) In Column A:A I want to select A3: the last cell in that column

with
data

in it (non-empty cell).

2) On this code = [ If ActiveCell.Row <> 1 Then ] I want to expand

it

to
off???

 

[Guest]

The answer to question 2 works great. Here is what I did with your answer to
question 1. Where is my mistake? I want it to copy everything from B3: the
last cell in Column B:B that is not blank???

Dim Istrow As Long
Istrow = Sheets("Sheet1").Cells(Rows.Count, "B:B").End(xlUp).Row
Range("B3" & Istrow).Copy

1. The following will return the last row number with data and susbstitue
into the select

Dim lstrow As Long
lstrow = Sheets("WSname").Cells(Rows.Count, "A").End(xlUp).Row
Range("A" & lstrow).Select

2. You need to be explicit about the second condition eg....

If ActiveCell.Row <> 1 or ActiveCell.Row <> 2 Then

Cheers
Nigel

1) In Column A:A I want to select A3: the last cell in that column with data
in it (non-empty cell).

2) On this code = [ If ActiveCell.Row <> 1 Then ] I want to expand it to
If ActiveCell.Row <> 1 or 2 Then. But the "or 2" is throwing things

off???

 

[Guest]

I altered my logic and made this work. The explicit was the issue I was
struggling with. What about an answer to my question 1?

2. You need to be explicit about the second condition eg....

If ActiveCell.Row <> 1 or ActiveCell.Row <> 2 Then

While it is not clear what the OP means exactly by 1 or 2, I would suspect
he wants something like

If ActiveCell.Row <> 1 and ActiveCell.Row <> 2 then

I say this, because I would be hard pressed to come up with an activecell
that did not meet one of the conditions of not being row 1 or not being row
2 as stated using an OR connector.


If I am right, then a less explicit (but equally effective) approach might
be

if ActiveCell.Row > 2 then

of
If ActiveCell.row >= 3 then

--
Regards,
Tom Ogilvy

1. The following will return the last row number with data and susbstitue
into the select

Dim lstrow As Long
lstrow = Sheets("WSname").Cells(Rows.Count, "A").End(xlUp).Row
Range("A" & lstrow).Select

2. You need to be explicit about the second condition eg....

If ActiveCell.Row <> 1 or ActiveCell.Row <> 2 Then

Cheers
Nigel

1) In Column A:A I want to select A3: the last cell in that column with data
in it (non-empty cell).

2) On this code = [ If ActiveCell.Row <> 1 Then ] I want to expand it to
If ActiveCell.Row <> 1 or 2 Then. But the "or 2" is throwing things

off???

 

[Nigel]

Try.....


Dim Istrow As Long
Istrow = Sheets("Sheet1").Cells(Rows.Count, "B").End(xlUp).Row
Range("B3:B" & Istrow).Copy

Cheers
Nigel

The answer to question 2 works great. Here is what I did with your answer to
question 1. Where is my mistake? I want it to copy everything from B3: the
last cell in Column B:B that is not blank???

Dim Istrow As Long
Istrow = Sheets("Sheet1").Cells(Rows.Count, "B:B").End(xlUp).Row
Range("B3" & Istrow).Copy

1. The following will return the last row number with data and susbstitue
into the select

Dim lstrow As Long
lstrow = Sheets("WSname").Cells(Rows.Count, "A").End(xlUp).Row
Range("A" & lstrow).Select

2. You need to be explicit about the second condition eg....

If ActiveCell.Row <> 1 or ActiveCell.Row <> 2 Then

Cheers
Nigel

1) In Column A:A I want to select A3: the last cell in that column

with
data

in it (non-empty cell).

2) On this code = [ If ActiveCell.Row <> 1 Then ] I want to expand it to
If ActiveCell.Row <> 1 or 2 Then. But the "or 2" is throwing things

off???