xlDown stops at next empty cell, not at last row of data
 

Hello everyone!

I have a macro that sifts through my data and copies what I need into
another worksheet. The problem is that when it tries to copy a column to the
other sheet, it only selects everything from the top down to the next empty
cell. (xlDown isn't very useful for this I think...) Roughly, here's what I
have:

----

Range("HourlyRate", Range("HourlyRate").End(xlEnd)).Copy
Sheets("AlphaList").Select
Range("J1").Select
ActiveSheet.Paste
ActiveWorkbook.Names.Add Name:="AHourlyRate",
RefersToR1C1:="=AlphaList!R1C10"

----

(HourlyRate is the "defined name" of the column header of the raw data that
I'm copying from.)

This code would basically only copy cells that are continuous (no blank
cells), however sometimes there are no Hourly Rates for people thus a blank
cell will be there.

Is there anything that I can do to alleviate this? ALSO, if it helps, there
is one column in my data that never has any blank spots. The "AddressNumber"
is that column and I copy it using the same method:

---

Range("AddressNumber", Range("AddressNumber").End(xlDown)).Copy
Sheets("AlphaList").Select
Range("G1").Select
ActiveSheet.Paste
ActiveWorkbook.Names.Add Name:="AAddressNumber",
RefersToR1C1:="=AlphaList!R1C7"

---

Can I incorporate the range of the last row of this column for the last row
of any other columns that I need to copy over?

xlDown stops at next empty cell, not at last row of data
 

Ronny,

Try replacing the copy line with the following:

Range("HourlyRate", Cells(Range("AddressNumber").End(xlDown).Row,
Range("HourlyRate").Column)).Copy

Mike



"Ronny Hamida" wrote:

Hello everyone!

I have a macro that sifts through my data and copies what I need into
another worksheet. The problem is that when it tries to copy a column to the
other sheet, it only selects everything from the top down to the next empty
cell. (xlDown isn't very useful for this I think...) Roughly, here's what I
have:

----

Range("HourlyRate", Range("HourlyRate").End(xlEnd)).Copy
Sheets("AlphaList").Select
Range("J1").Select
ActiveSheet.Paste
ActiveWorkbook.Names.Add Name:="AHourlyRate",
RefersToR1C1:="=AlphaList!R1C10"

----

(HourlyRate is the "defined name" of the column header of the raw data that
I'm copying from.)

This code would basically only copy cells that are continuous (no blank
cells), however sometimes there are no Hourly Rates for people thus a blank
cell will be there.

Is there anything that I can do to alleviate this? ALSO, if it helps, there
is one column in my data that never has any blank spots. The "AddressNumber"
is that column and I copy it using the same method:

---

Range("AddressNumber", Range("AddressNumber").End(xlDown)).Copy
Sheets("AlphaList").Select
Range("G1").Select
ActiveSheet.Paste
ActiveWorkbook.Names.Add Name:="AAddressNumber",
RefersToR1C1:="=AlphaList!R1C7"

---

Can I incorporate the range of the last row of this column for the last row
of any other columns that I need to copy over?

xlDown stops at next empty cell, not at last row of data
 

Range("HourlyRate", Cells(65536, Range("HourlyRate").Column).End(xlUp)).Copy

HTH,
Bernie
MS Excel MVP


"Ronny Hamida" wrote in message
...
Hello everyone!

I have a macro that sifts through my data and copies what I need into
another worksheet. The problem is that when it tries to copy a column to the
other sheet, it only selects everything from the top down to the next empty
cell. (xlDown isn't very useful for this I think...) Roughly, here's what I
have:

----

Range("HourlyRate", Range("HourlyRate").End(xlEnd)).Copy
Sheets("AlphaList").Select
Range("J1").Select
ActiveSheet.Paste
ActiveWorkbook.Names.Add Name:="AHourlyRate",
RefersToR1C1:="=AlphaList!R1C10"

----

(HourlyRate is the "defined name" of the column header of the raw data that
I'm copying from.)

This code would basically only copy cells that are continuous (no blank
cells), however sometimes there are no Hourly Rates for people thus a blank
cell will be there.

Is there anything that I can do to alleviate this? ALSO, if it helps, there
is one column in my data that never has any blank spots. The "AddressNumber"
is that column and I copy it using the same method:

---

Range("AddressNumber", Range("AddressNumber").End(xlDown)).Copy
Sheets("AlphaList").Select
Range("G1").Select
ActiveSheet.Paste
ActiveWorkbook.Names.Add Name:="AAddressNumber",
RefersToR1C1:="=AlphaList!R1C7"

---

Can I incorporate the range of the last row of this column for the last row
of any other columns that I need to copy over?

xlDown stops at next empty cell, not at last row of data
 

Lengthy, but this one is perfect! Thank you!

"crazybass2" wrote:

Ronny,

Try replacing the copy line with the following:

Range("HourlyRate", Cells(Range("AddressNumber").End(xlDown).Row,
Range("HourlyRate").Column)).Copy

Mike



"Ronny Hamida" wrote:

Hello everyone!

I have a macro that sifts through my data and copies what I need into
another worksheet. The problem is that when it tries to copy a column to the
other sheet, it only selects everything from the top down to the next empty
cell. (xlDown isn't very useful for this I think...) Roughly, here's what I
have:

----

Range("HourlyRate", Range("HourlyRate").End(xlEnd)).Copy
Sheets("AlphaList").Select
Range("J1").Select
ActiveSheet.Paste
ActiveWorkbook.Names.Add Name:="AHourlyRate",
RefersToR1C1:="=AlphaList!R1C10"

----

(HourlyRate is the "defined name" of the column header of the raw data that
I'm copying from.)

This code would basically only copy cells that are continuous (no blank
cells), however sometimes there are no Hourly Rates for people thus a blank
cell will be there.

Is there anything that I can do to alleviate this? ALSO, if it helps, there
is one column in my data that never has any blank spots. The "AddressNumber"
is that column and I copy it using the same method:

---

Range("AddressNumber", Range("AddressNumber").End(xlDown)).Copy
Sheets("AlphaList").Select
Range("G1").Select
ActiveSheet.Paste
ActiveWorkbook.Names.Add Name:="AAddressNumber",
RefersToR1C1:="=AlphaList!R1C7"

---

Can I incorporate the range of the last row of this column for the last row
of any other columns that I need to copy over?

xlDown stops at next empty cell, not at last row of data
 

Bernie -

Believe it or not, I had tried this before, however if the last row has a
blank HourlyRate, it will skip it and go to the next record above...

Thanks for the tip, though! This particular statement works for copying the
"AddressNumber" so I'll use it there! :)

Cheers,

Ronny

"Bernie Deitrick" wrote:

Range("HourlyRate", Cells(65536, Range("HourlyRate").Column).End(xlUp)).Copy

HTH,
Bernie
MS Excel MVP


"Ronny Hamida" wrote in message
...
Hello everyone!

I have a macro that sifts through my data and copies what I need into
another worksheet. The problem is that when it tries to copy a column to the
other sheet, it only selects everything from the top down to the next empty
cell. (xlDown isn't very useful for this I think...) Roughly, here's what I
have:

----

Range("HourlyRate", Range("HourlyRate").End(xlEnd)).Copy
Sheets("AlphaList").Select
Range("J1").Select
ActiveSheet.Paste
ActiveWorkbook.Names.Add Name:="AHourlyRate",
RefersToR1C1:="=AlphaList!R1C10"

----

(HourlyRate is the "defined name" of the column header of the raw data that
I'm copying from.)

This code would basically only copy cells that are continuous (no blank
cells), however sometimes there are no Hourly Rates for people thus a blank
cell will be there.

Is there anything that I can do to alleviate this? ALSO, if it helps, there
is one column in my data that never has any blank spots. The "AddressNumber"
is that column and I copy it using the same method:

---

Range("AddressNumber", Range("AddressNumber").End(xlDown)).Copy
Sheets("AlphaList").Select
Range("G1").Select
ActiveSheet.Paste
ActiveWorkbook.Names.Add Name:="AAddressNumber",
RefersToR1C1:="=AlphaList!R1C7"

---

Can I incorporate the range of the last row of this column for the last row
of any other columns that I need to copy over?

xlDown stops at next empty cell, not at last row of data
 

Ronny,

Obviously, I'm not developing a full application for you. Typically, you can use conditional
statements to check for HourlyRate being blank, or .... but you get the idea...

HTH,
Bernie
MS Excel MVP


"Ronny Hamida" wrote in message
...
Bernie -

Believe it or not, I had tried this before, however if the last row has a
blank HourlyRate, it will skip it and go to the next record above...

Thanks for the tip, though! This particular statement works for copying the
"AddressNumber" so I'll use it there! :)

Cheers,

Ronny

"Bernie Deitrick" wrote:

Range("HourlyRate", Cells(65536, Range("HourlyRate").Column).End(xlUp)).Copy

HTH,
Bernie
MS Excel MVP


"Ronny Hamida" wrote in message
...
Hello everyone!

I have a macro that sifts through my data and copies what I need into
another worksheet. The problem is that when it tries to copy a column to the
other sheet, it only selects everything from the top down to the next empty
cell. (xlDown isn't very useful for this I think...) Roughly, here's what I
have:

----

Range("HourlyRate", Range("HourlyRate").End(xlEnd)).Copy
Sheets("AlphaList").Select
Range("J1").Select
ActiveSheet.Paste
ActiveWorkbook.Names.Add Name:="AHourlyRate",
RefersToR1C1:="=AlphaList!R1C10"

----

(HourlyRate is the "defined name" of the column header of the raw data that
I'm copying from.)

This code would basically only copy cells that are continuous (no blank
cells), however sometimes there are no Hourly Rates for people thus a blank
cell will be there.

Is there anything that I can do to alleviate this? ALSO, if it helps, there
is one column in my data that never has any blank spots. The "AddressNumber"
is that column and I copy it using the same method:

---

Range("AddressNumber", Range("AddressNumber").End(xlDown)).Copy
Sheets("AlphaList").Select
Range("G1").Select
ActiveSheet.Paste
ActiveWorkbook.Names.Add Name:="AAddressNumber",
RefersToR1C1:="=AlphaList!R1C7"

---

Can I incorporate the range of the last row of this column for the last row
of any other columns that I need to copy over?