dynamic range selection with a macro

R

Ray Pendergast

I'm trying to convert a CSV file into something I can use in a database. The
report comes broken into sections. Each section has header data that applies
to all the records beneath it, but is not included in those records. I need
to get certain values from the headers copied into the records directly
beneath. There's no set number of records in any one section, so the macro
needs to select its ranges dynamically.

Here's the setup and the key strokes I used to record a macro:
C1 has the first header, C10 has the second, C25 has the third, and so on.
Cells C2:C9 and C11:C24, etc. are blank. I want to copy the value in C1 to
C2:C9, the value in C10 to C11:C24, etc.

C1 is active when I start recording.
[Ctrl]+[C] (copies C1 to clipboard)
Hold [Shift]
[End]
[Down]
[Up]
Release [Shift] (selects C1:C9)
[Enter] (pastes value in C1 to all cells in range)
[End]
[Down]
End recording

Here is the code that resulted from the macro I recorded:

Selection.Copy
Range("C1:C9").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Selection.End(xlDown).Select

When I do those exact keystrokes by hand, I end up at C10 and can repeat
those same steps. But I have hundreds of headers to copy to thousands of
lines, and there are a different number of records associated with each
header.

I tried replacing Range("C1:C9") with Selection.End(xlDown), but that didn't
work. I tried improvising VB code by using Selection.End(xlDown)(x1Up), but
that didn't work. (This paragraph is irrelevant, except my pride compels me
to show that I at least tried something logical before turning to you...)

Can anyone suggest a way to select a dynamic range of cells to accomplish
what I'm trying to do? Thanks in advance.
Ray
 
E

Earl Kiosterud

Ray,

I think you need to tell us how your records are laid out, and what you want
done. Telling us just your steps makes us guess at a lot of stuff. Set
your newsreader (Outlook Express - whatever) to use Courier so the spacing
won't be bizarre.
 
G

Gord Dibben

Ray

Select column C

Edit>Go To>Special>Blanks>OK

In the active cell enter an = sign.

Point to the cell above(C1 presumably) and hit CRTL + ENTER keys.

Your C1 will copy down to C9. C10 will copy down to C24 etc.

Gord Dibben Excel MVP
 
R

Ray Pendergast

That did it! No macro needed, either. It has been a long time since I've
been this excited about learning a new feature of Excel. Thank you very much!

Gord Dibben said:
Ray

Select column C

Edit>Go To>Special>Blanks>OK

In the active cell enter an = sign.

Point to the cell above(C1 presumably) and hit CRTL + ENTER keys.

Your C1 will copy down to C9. C10 will copy down to C24 etc.

Gord Dibben Excel MVP

I'm trying to convert a CSV file into something I can use in a database. The
report comes broken into sections. Each section has header data that applies
to all the records beneath it, but is not included in those records. I need
to get certain values from the headers copied into the records directly
beneath. There's no set number of records in any one section, so the macro
needs to select its ranges dynamically.

Here's the setup and the key strokes I used to record a macro:
C1 has the first header, C10 has the second, C25 has the third, and so on.
Cells C2:C9 and C11:C24, etc. are blank. I want to copy the value in C1 to
C2:C9, the value in C10 to C11:C24, etc.

C1 is active when I start recording.
[Ctrl]+[C] (copies C1 to clipboard)
Hold [Shift]
[End]
[Down]
[Up]
Release [Shift] (selects C1:C9)
[Enter] (pastes value in C1 to all cells in range)
[End]
[Down]
End recording

Here is the code that resulted from the macro I recorded:

Selection.Copy
Range("C1:C9").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Selection.End(xlDown).Select

When I do those exact keystrokes by hand, I end up at C10 and can repeat
those same steps. But I have hundreds of headers to copy to thousands of
lines, and there are a different number of records associated with each
header.

I tried replacing Range("C1:C9") with Selection.End(xlDown), but that didn't
work. I tried improvising VB code by using Selection.End(xlDown)(x1Up), but
that didn't work. (This paragraph is irrelevant, except my pride compels me
to show that I at least tried something logical before turning to you...)

Can anyone suggest a way to select a dynamic range of cells to accomplish
what I'm trying to do? Thanks in advance.
Ray
Click to expand...
Click to expand...
 
G

Gord Dibben

Thanks for the feedback Ray.

Gord

That did it! No macro needed, either. It has been a long time since I've
been this excited about learning a new feature of Excel. Thank you very much!

Gord Dibben said:
Ray

Select column C

Edit>Go To>Special>Blanks>OK

In the active cell enter an = sign.

Point to the cell above(C1 presumably) and hit CRTL + ENTER keys.

Your C1 will copy down to C9. C10 will copy down to C24 etc.

Gord Dibben Excel MVP

I'm trying to convert a CSV file into something I can use in a database. The
report comes broken into sections. Each section has header data that applies
to all the records beneath it, but is not included in those records. I need
to get certain values from the headers copied into the records directly
beneath. There's no set number of records in any one section, so the macro
needs to select its ranges dynamically.

Here's the setup and the key strokes I used to record a macro:
C1 has the first header, C10 has the second, C25 has the third, and so on.
Cells C2:C9 and C11:C24, etc. are blank. I want to copy the value in C1 to
C2:C9, the value in C10 to C11:C24, etc.

C1 is active when I start recording.
[Ctrl]+[C] (copies C1 to clipboard)
Hold [Shift]
[End]
[Down]
[Up]
Release [Shift] (selects C1:C9)
[Enter] (pastes value in C1 to all cells in range)
[End]
[Down]
End recording

Here is the code that resulted from the macro I recorded:

Selection.Copy
Range("C1:C9").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Selection.End(xlDown).Select

When I do those exact keystrokes by hand, I end up at C10 and can repeat
those same steps. But I have hundreds of headers to copy to thousands of
lines, and there are a different number of records associated with each
header.

I tried replacing Range("C1:C9") with Selection.End(xlDown), but that didn't
work. I tried improvising VB code by using Selection.End(xlDown)(x1Up), but
that didn't work. (This paragraph is irrelevant, except my pride compels me
to show that I at least tried something logical before turning to you...)

Can anyone suggest a way to select a dynamic range of cells to accomplish
what I'm trying to do? Thanks in advance.
Ray
Click to expand...
Click to expand...
Click to expand...
 
Top