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

[Guest]

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"

 

[Guest]

Ronny,

Try replacing the copy line with the following:

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

Mike

 

[Bernie Deitrick]

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

HTH,
Bernie
MS Excel MVP

 

[Guest]

Lengthy, but this one is perfect! Thank you!

Ronny,

Try replacing the copy line with the following:

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

Mike

 

[Guest]

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]

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