0

I am replicating a VBA script for consolidating 4 tabs of similar data into one tab. Having a little trouble on the copy/paste portion as this code ignores blank cells on Col A. I am attempting to bypass that by using the code to find range based on Col C and then offsetting the resulting range to add back Col A and Col B. It's not really working though and I am not sure how to proceed.

original script for coopy + paste: 

 'Data range in worksheet - starts from second row as first rows are the header rows in all worksheets
    Set rng = sht.Range(sht.Cells(2, 1), sht.Cells(65536, 1).End(xlUp).Resize(, colCount))

My modification:

'Data range in worksheet - starts from second row as first rows are the header rows in all worksheets
    Set rng = sht.Range(sht.Cells(2, 3), sht.Cells(65536, 3).End(xlUp).Resize(, colCount))
    Set rng = rng.Offset(-2,).Resize(,2)

Data looks like this. I want to copy up to C24 but the script only copies up to C15 due to blank cells in Col A.

enter link description here enter image description here

Alan
  • 1
  • 4
  • It looks like it should work if you remove `.Resize(, colCount)`. How was `colCount` defined? –  Feb 07 '17 at 22:28
  • You have a line of code that uses `Set`. You copy it and remove `Set`. While it's possible that `rng` is `Variant` and you first use it in the reference context and then kind of confusingly assign `rng.Value = rng.Offset(-2,).Resize(,2).Value`, it's more likely that you should [put the `Set` back](http://stackoverflow.com/a/20763733/11683). – GSerg Feb 07 '17 at 22:29
  • The `rng = rng.Offset(-2,).Resize(,2)` should be `Set rng = rng.Offset(-2,).Resize(,2)`. You need to `Set` VBA objects. –  Feb 07 '17 at 22:29
  • I've tried that and it gave me a Compile error: syntax error on that line Set rng = rng.Offset(-2,).Resize(,2) – Alan Feb 07 '17 at 22:31
  • Also you should not have a trailing comma with omitted parameters. Remove the comma. – GSerg Feb 07 '17 at 22:32
  • yes, omitted the comma Set rng = rng.Offset(-2).Resize(,2) now it gave a Run-time error '1004': Application-defined or object-defined error – Alan Feb 07 '17 at 22:34
  • Hmm, the error still points to this line of code though, would there have been a better way to make this code? Sorry I am just starting off on VBA coding – Alan Feb 07 '17 at 22:40
  • 1
    I was saying that the issue you presented in the question is solved (and really qualifies as a typo). The issue you are having now is a different one and it comes from your program logic that only you understand. After your first assignment `rng` contains a range that cannot be moved two rows up which is because it starts at `A2`. – GSerg Feb 07 '17 at 22:46

1 Answers1

0

You have:

Set rng = sht.Range(sht.Cells(2, 3), sht.Cells(65536, 3).End(xlUp).Resize(, colCount))
Set rng = rng.Offset(-2,).Resize(,2)

The top-left cell of rng is at (2, 3) or C2 in A1-notation then you offset by (-2, 0) which leaves the top-left cell at (0, 3). The top-left cell in the entire worksheet is always (1, 1) so any row or column index less than 1 is an error.

As you are trying to offset by columns, you probably want (assuming that colCount is set correctly - it looks like it should equal 3):

Set rng = sht.Range(sht.Cells(2, 3), sht.Cells(65536, 3).End(xlUp))
Set rng = rng.Offset(0, -2).Resize(, colCount)

I've specified a row offset of 0 instead of leaving it blank as, to me, it makes it easier to read but other may prefer Offset(, -2) instead. Resize works differently so we need to leave the row parameter blank on that one

barrowc
  • 10,444
  • 1
  • 40
  • 53
  • Thanks this worked! moving colCount and reversing the offset took away all the errors! – Alan Feb 08 '17 at 14:36