Copy Only Used Cells In Column

Re: Copy Only Used Cells In Column

re:"What did you mean when you said it wouldn't work since the code will copy the third column?" in msg #15.

Well, I said: "if the usedrange leftmost column is not column A then this code will copy the third column of that usedrange which won't be column C".

Column C, as everyone knows, is the third column on any spreadsheet. In the code we've referred to it as Range("C:C") or Columns("C:C"). However, maybe everyone doesn't know that it's really referring to the third column of what immediately precedes it, normally it's 'ActiveSheet', explicitly or implicitly ('cos you don't need the ActiveSheet bit). But if, as we have done, we precede it with UsedRange, then it's the third column of the used range, look, I'll show you:

Open a new, virgin, sheet. Select say cells D2:H7, type a few characters on the keyboard then holding the control key down press the Enter key. You should now see that range filled with whatever you typed. Now click a cell elsewhere on the sheet to deselect the block. Go to the vbe and in the Immediate window type the following:

Activesheet.usedrange.select

and press enter, then take a peep at the sheet. Hopefully D2:H7 is selected. Note that the usedrange does not start in column A of the whole sheet. Click elsewhere again to deselect. Once again go into the immediate pane and type:

Activesheet.usedrange.columns("C:C").select

and press enter. Again take a look at the sheet. Instead of cells of the expected column C being selected you've got cells in column F selected, that's the third column of the Range D2:H7 (the used range). I hope this illustrates graphically what I was trying to say.

It's not common that the used range does not extend to column A of the whole sheet, but could easily happen if vba code adds a sheet and plonks some data in the middle of it somewhere.
Just something to be aware of, that's all.

p45cal

Re: Copy Only Used Cells In Column

Yes, what needs to be kept in mind is that when UsedRange is used to preceed a range Object, it becomes relevant to the used range.

If we assume the active sheet is new and ONLY has data starting from B1:B10;

Msgbox ActiveSheet.UsedRange.Columns(2).Address

Will return "C1:C10". Where-as;

Msgbox ActiveSheet.Columns(2).Address

Will return "B:B"

And going back to this for a moment;

Quote

Originally Posted by Reafidy quoting dmcritchie
Formatting will not affect the last cell indication, but will affect the size of a spreadsheet. It is best to format entire rows and columns than to format cells individually. Individual cell formatting can be very costly.

Add a new Worksheet and format all of Column "A", now format all of row 10 the same and run, this code;

MsgBox ActiveSheet.UsedRange.Address

Excel now THINKS the used range is A10, yet we have nothing in that cell, or any other cell. So I still believe the statement quoted above is false, or at best misleading.

Re: Copy Only Used Cells In Column

Quote from p45cal

... maybe everyone doesn't know that it's really referring to the third column of what immediately precedes it

Indeed. The used range of column C is,

Intersect(ActiveSheet.Usedrange, Columns("C"))

.

Re: Copy Only Used Cells In Column

Quote from Dave Hawley

If we assume the active sheet is new and ONLY has data starting from B1 onwards;

Code

Msgbox ActiveSheet.UsedRange.Columns(2).Address

Will return "A:A".

I hadn't gone as far down the line in my thinking on this, and was ready to accept this but a bit of exploration gave a different result, viz.:
$C$1:$C$14
(I hadn't filled the entire column B with data, only B1:G14.) XP Home, Excel 2003.

By the same token, should

Msgbox ActiveSheet.UsedRange.Range("A1").Address

return $A$1? (It returned $B$1 here)
p45cal [SIZE="1"]..ready to accept I've lost the plot![/SIZE]

Re: Copy Only Used Cells In Column

Don't worry, it's me that has lost the plot I have edited my post above so it makes sense.