0

The code I'm using is:

Sub selectrange()

Dim rngSource As Range, rngDest As Range
Set rngSource = Range(Range("A1"), Range("A1").End(xlDown).End(xlToRight))
'Only used to check the data being copied
rngSource.Select
Set rngDest = Range("A1").End(xlToRight).Offset(0, 1)
rngSource.Copy
rngDest.PasteSpecial

End Sub

The idea is to have the code select all the rows/columns with data and paste them in the next available section of another workbook. But by running this code only some of the columns are copied and pasted.

The data in question has 12 columns (A to L), some of them empty aside from the header. When I used End(xlToRight).End(xlDown) the Selection stopped at the first empty cell in any row, so instead I did End(xlDown) on column A which contains dates, then End(xlToRight) to include all possible columns regardless if they have data or not.

However when doing this, the selection arbitrarily stops at column E, despite the fact column F in populated and without blank cells...

If anyone has any ideas it would be greatly appreciated!

Thanks

Community
  • 1
  • 1
vlad88667
  • 59
  • 2
  • 9
  • Your last row isn't filled out all the way to the column you want to highlight. Last row is governing where it stops. – Marc Mar 03 '15 at 21:50
  • Use info from here: http://stackoverflow.com/questions/11926972/excel-vba-finding-the-last-column-with-data – Marc Mar 03 '15 at 21:54
  • 1
    `End(direction)` behaves the same way as using `Ctrl+[arrow key]` in the UI. If you have no fully-blank rows or columns the `Range("A1").CurrentRegion` should select the whole block (same as `Ctrl+A`) – Tim Williams Mar 03 '15 at 21:55
  • @TimWilliams Brilliant that should indeed work in my case! Thank you – vlad88667 Mar 03 '15 at 22:00

2 Answers2

1

Personally I prefer the usedrange of the activesheet than the CurrentRegion of a range:

here are the results from a sheet I knocked up:

?activesheet.usedrange.address

$A$1:$H$9

?Activecell.currentregion.address

$A$4:$B$6

You can see the Used Range is much larger than the current region.

However, to delve even further, being that you know the number of columns AND you know column A always has data you are best to do this:

Set rngSource = Range("A1:L" & Range("A" & Rows.count).end(xlup).row)
Dan Donoghue
  • 6,056
  • 2
  • 18
  • 36
  • Thanks for the info Dan, I might use it for another project. However in this one the number of columns might change so CurrentRegion seems appropriate and is working so far. – vlad88667 Mar 04 '15 at 10:19
0

As Dan alluded to, you can use the UsedRange property for this task. If you do a lot of VBA, you will find getting Range-Info is something you do over and over again. You may (or may not) find this useful: https://stackoverflow.com/a/28502702/4487534

Community
  • 1
  • 1
Pillgram
  • 804
  • 6
  • 11