Code help please!

rjc4

Well-known Member
Joined
Nov 6, 2004
Messages
502
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
 

Some videos you may like

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.

Crow_23

Board Regular
Joined
Feb 17, 2005
Messages
183
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

MrExcel MVP
Joined
Jun 19, 2002
Messages
10,014
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

Well-known Member
Joined
Nov 6, 2004
Messages
502
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

MrExcel MVP
Joined
Jun 19, 2002
Messages
10,014
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.
Code:
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
Code:
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

Well-known Member
Joined
Nov 6, 2004
Messages
502
Hi Greg,
Thanks for your comments and code options. You have been most helpful.
Much appreciated,
Kind regards,
Rick
 

Watch MrExcel Video

Forum statistics

Threads
1,122,207
Messages
5,594,843
Members
413,944
Latest member
3xc3ln00b

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top