Code help please!

[rjc4]

Hello All,

I have 4 columns of data always starting in column C
but can exist on any number of rows. The columns are
headed as the sample below.

There is always data above and below my data block
which can push my block up or down in column C.

How do I extract just the last 2 columns E and F and
copy the contents to cell S1 when the number of rows
is not fixed.

I would appreciate any help to do this please. I've
managed to get the headings but don't know how to
cope with the variable rows.
Thankyou in advance.

Regards,
RC


Column C ColD ColE ColF

OptionsName Avg Pos Neg
Slimsen J 6.6 10.2 2.7
Campbell C 7.8 6.4 1.7
Louis N
Leuwis P 3.8 3.6 1.3
Hoover B 14.6 13.6 4.1
Preen T
Bester S 15.3 22.4 5.7
Bosten G 3.2 3 1.3
Jareete H 24.9 28.9 5.7
Barton W 26.6 36.8 11.3
Astors B 8.9 9.8 3.1
Williams M 18.4 27.4 8.1

 

[Crow_23]

Hey RJ,

Is this all your looking for?

Sub CutANDPaste()

Columns("E:F").Select
Selection.Copy
ActiveWindow.SmallScroll ToRight:=9
Range("S1").Select
ActiveSheet.paste

End Sub


Ed

 

[Greg Truby]

How do you know the boundaries of your "data block"?

1. Are there blank rows preceding and following your "data block"?
2. Are columns A & B empty?
3. Is column G empty?

 

[rjc4]

Hi Ed and Greg,
This is what I've come up with. It works as required and finds the block of data wherever it is on the sheet. If you can see ways to streamline the code please post back.
Thanks for your replies.
Cheers!
RC

Code:
Range("C1").Select
Cells.Find(what:="OptionsName", After:=ActiveCell).Activate
ActiveCell.Offset(0, 0).Resize(1, 1).Select 'Goes to Name 1=down
StartCell = ActiveCell.Address
Range("C1").Select
Cells.Find(what:="OptionsName", After:=ActiveCell, LookIn:=xlFormulas, lookat:= _
xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:= _
True).Activate
Selection.End(xlDown).Select
ActiveCell.Offset(0, 3).Resize(1, 1).Select
EndCell = ActiveCell.End(xlDown)
EndCell = ActiveCell.Address
Range(StartCell & ":" & EndCell).Copy [S1]

 

[Greg Truby]

rc,

The following examples appear to work okay in testing. You have a comment on the line:
ActiveCell.Offset(0, 0).Resize(1, 1).Select 'Goes to Name 1=down
that implies that this would be activating the cell below the found cell. Actually that should not be what happens, your offset is (0,0).

The code below works like the code you posted, which in this case would only copy the first two lines because there is no numeric data for the third name on the list, Louis N.

Sub test1()
    [S1].CurrentRegion.Clear
    
    Dim rngStart As Range, rngEnd As Range
    
    Set rngStart = Cells.Find(What:="OptionsName", After:=Range("C1"))
    Set rngEnd = rngStart.Offset(, 3).End(xlDown)
    Range(rngStart, rngEnd).Copy [S1]
End Sub

Note the change in results if we alter the sequence of commands in setting rngEnd. This time the macro copies all names on the list because it first runs down the list of names and then offsets 3 columns

Sub test2()
    [S1].CurrentRegion.Clear

    Dim rngStart As Range, rngEnd As Range
    
    Set rngStart = Cells.Find(What:="OptionsName", After:=Range("C1"))
    Set rngEnd = rngStart.End(xlDown).Offset(, 3)
    Range(rngStart, rngEnd).Copy [S1]
End Sub

Also, if I may be so bold, read up on (Hungarian/Reddick's) naming conventions. For example, in the code you posted your variable StartCell is a string, but one must pay close attention to your code in order to know this. If I were just reading code quickly, I would normally assume a variable of this name to be a range object, not a string.

Regards,

 

[rjc4]

Hi Greg,
Thanks for your comments and code options. You have been most helpful.
Much appreciated,
Kind regards,
Rick