Excel VBA copy column of non-contiguous data

[Hcleslie]

What is the best way to select and copy a column of non-contiguous data without copying the first row that contains a column heading?

Thanks

 

[SkipVought]

Hi,

Please describe and example of your column of non-contiguous data.

What row is column heading in?
What row does data start in?
How many empty rows?
How many total rows?

Skip,
[sub]
[red]Be advised:[/red] When you ignite a firecracker in a bowl of vanilla, chocolate & strawberry ice cream, you get...
Neopolitan Blownapart! [/sub]

 

[Hcleslie]

The column heading will be in row 1.
The data will start in row 2, but might be blank.
The number of empty rows will vary. The data source was not originally a required field.

Total rows should be less than 5,000.

Thank you

 

[SkipVought]

Sub NameColumn(iCol As Integer)
    Range(Cells(1, iCol), _
        Cells(Cells(Cells.Rows.Count, iCol).End(xlUp).Row, iCol)).CreateNames _
            Top:=True, Left:=False, Bottom:=False, Right:=False
End Sub

Skip,
[sub]
[red]Be advised:[/red] When you ignite a firecracker in a bowl of vanilla, chocolate & strawberry ice cream, you get...
Neopolitan Blownapart! [/sub]

 

[xlbo]

Range(cells(2,1),cells(cells(65536,1).end(xlup).row,1)).copy

- assumes column to copy is "A"

Rgds, Geoff

"Three things are certain: Death, taxes and lost data. Guess which has occurred"

Please read FAQ222-2244 before you ask a question

 

[SkipVought]

Geoff, where was my head? COPY!

Skip,
[sub]
[red]Be advised:[/red] When you ignite a firecracker in a bowl of vanilla, chocolate & strawberry ice cream, you get...
Neopolitan Blownapart! [/sub]

 

[xlbo]

Skip - I was wondering but I thought that there would be some method in the madness......obviously your head was by the pool, supping a margherita !!

Rgds, Geoff

"Three things are certain: Death, taxes and lost data. Guess which has occurred"

Please read FAQ222-2244 before you ask a question

 

[SkipVought]

What? You think I'm a parrot-head???

Skip,
[sub]
[red]Be advised:[/red] When you ignite a firecracker in a bowl of vanilla, chocolate & strawberry ice cream, you get...
Neopolitan Blownapart! [/sub]

 

[xlbo]

well - a banana daquiri then ;-)

Rgds, Geoff

"Three things are certain: Death, taxes and lost data. Guess which has occurred"

Please read FAQ222-2244 before you ask a question

 

[Hcleslie]

Skip and Geoff,

Thank you both for the information and the humor.

Skip, I am not able to run you code, when I paste this into the VBE and try to run, it doesn't process or recognize this as a macro. I understand that I need to finish this up with the copy command, but I must be leaving out a step to have this recognized as a macro?

Geoff, your code looks very clean and runs very fast. As you mentioned in your response, it only picks first column. I have changed the numbers in hopes I could modify this to pick other columns as well, but I was only able to have it select column one and adjacent columns. Could you please explain logic and structure so I could modify this to run on other columns?

Thank you,

Charlie

 

[SkipVought]

You have to pass the column number like this...

   CopyColumn 2

for instance for column B -- or if you'ld rather use letters...

   CopyColumn Cells(1,"B").column

Sub CopyColumn(iCol As Integer)
    Range(Cells(1, iCol), _
        Cells(Cells(Cells.Rows.Count, iCol).End(xlUp).Copy
End Sub

Skip,
[sub]
[red]Be advised:[/red] When you ignite a firecracker in a bowl of vanilla, chocolate & strawberry ice cream, you get...
Neopolitan Blownapart! [/sub]

 

[xlbo]

In terms of mine, you would simply change the "1"s ie to get it to work on column 2 (or "B"), this:

Range(cells(2,1),cells(cells(65536,1).end(xlup).row,1)).copy

would become

Range(cells(2,2),cells(cells(65536,2).end(xlup).row,2)).copy

Rgds, Geoff

"Three things are certain: Death, taxes and lost data. Guess which has occurred"

Please read FAQ222-2244 before you ask a question