CODEWITHSUNDEEP
excelvba
vlad88667
vlad88667

Reputation: 59

VBA : End(xlToRight) doesn't include all columns?

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

Upvotes: 0

Views: 9714

Answers (2)

Pillgram
Pillgram

Reputation: 824

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

Upvotes: 0

Dan Donoghue
Dan Donoghue

Reputation: 6186

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)

Upvotes: 1

Related Questions