0

I need to add a piece of code to my macro that copies and pastes a range of data.

The problem that I am having is that the data can start and finish on any row.

Is it possible to do this?

e.g. Currently the data starts in cell C10 and ends in cell I20. But next month it could start in C5 and end in I40.

The column range will always remain the same i.e C:I, but the row will change each time.

How to a add code to search for the first instance of data in Column C and copy all the data that follows?

Thanks

Community
  • 1
  • 1
Hazel Popham
  • 187
  • 1
  • 12
  • Yes it's possible, also pretty easy to do. Just put the first row and last row value in variables and enter the value manually when it as to be change. Please post a piece of code you tried so we can help you – phil652 Nov 26 '15 at 15:01
  • Possible duplicate of [How can I find last row that contains data in the Excel sheet with a macro?](http://stackoverflow.com/questions/71180/how-can-i-find-last-row-that-contains-data-in-the-excel-sheet-with-a-macro) – GSerg Nov 26 '15 at 15:01

3 Answers3

1

Here are some code snippets that might help

Get the first cell in C

If Range("C1").Value <> "" Then
    Set firstcell = Range("C1")
Else
    Set firstcell = Range("C1").End(xlDown)
End If

I test C1 because it will go all the way to the bottom if that is the only cell in C with values.

After that, to get the bottom cell after that

Set lastcell = Cells(Rows.Count, "I").End(xlUp)
Bob Phillips
  • 437
  • 1
  • 3
  • 7
0

Another way might be to use a named range in the spreadsheet to define the range you want to copy.

=OFFSET(C1,MATCH(TRUE,INDEX(NOT(ISBLANK(C:C)),0),0)-1,0,COUNTA(C:C),7)

That formula will select any number of data rows starting anywhere in column C. It doesn't account for header rows or any other content. If your data would always start in at least, say, row 5, then you could adjust the C1 value and use C5:C1000 instead of C:C. Then, in the VBA you can just use Range("namedRange"). Copy instead of having to work out the start and end points.

joan16v
  • 5,055
  • 4
  • 49
  • 49
Chris Geatch
  • 113
  • 6
  • Actually, it sounds like you're getting data incoming from somewhere, so you're not going to want to go and create a named range every time, so maybe this isn't much use. Sorry. – Chris Geatch Nov 26 '15 at 16:55
-1

If you highlight the required cells before running the macro, then you can use Selection to refer to the selected range

ie, instead of

 Range("C10:i20").Clear

you can write

Selection.Clear
Andy Brazil
  • 1