Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to auto count data in an empty cell to be 0. Blank cell=0 | Excel Discussion (Misc queries) | |||
End(xldown) not always going to last cell? | Excel Programming | |||
Unable to get last filled cell address from Range.End(xlDown) method | Excel Programming | |||
Enter data in next empty row after cell name | Excel Programming | |||
Going down until the data stops.... | Excel Programming |