All,

I am trying to determine the code to select the 2 last empty columns in
Excel via VBA. The only code I can find is
Range("IV5").End(xlToLeft).Select the only reason for that is row 5
contains data and 1-4 don't, thus returning results further left than I
wanted when doing a paste.

Again I am trying to select the last 2 empty columns in a worksheet. What
would be the most effective way to do this?

Regards,

Nathan

Nathan

Try this

Sub FindLastColumn()

Dim rFound As Range

Set rFound = Sheet1.Cells.Find("*", Sheet1.Cells(1, 1), , , xlByColumns,
xlPrevious)

rFound.Offset(0, 1).Resize(1, 2).EntireColumn.Select

End Sub

--
Dick Kusleika
Excel MVP
Daily Dose of Excel
www.dicks-blog.com

Nathan Bell wrote:
All,

I am trying to determine the code to select the 2 last empty
columns in Excel via VBA. The only code I can find is
Range("IV5").End(xlToLeft).Select the only reason for that is row 5
contains data and 1-4 don't, thus returning results further left than
I wanted when doing a paste.

Again I am trying to select the last 2 empty columns in a worksheet.
What would be the most effective way to do this?

Regards,

Nathan

try either

Sub selectlast2emptycolumns() 'two ways

'x = Cells(5, Columns.Count).End(xlToLeft).Address
'Range(x).Offset(0, 1).Resize(1, 2).EntireColumn.Select

x = Cells(5, Columns.Count).End(xlToLeft).Column + 1
Range(Cells(5, x), Cells(5, x + 1)).EntireColumn.Select

End Sub

--
Don Guillett
SalesAid Software

"Nathan Bell" wrote in message
...
All,

I am trying to determine the code to select the 2 last empty columns
in
Excel via VBA. The only code I can find is
Range("IV5").End(xlToLeft).Select the only reason for that is row 5
contains data and 1-4 don't, thus returning results further left than I
wanted when doing a paste.

Again I am trying to select the last 2 empty columns in a worksheet. What
would be the most effective way to do this?

Regards,

Nathan

Don,

Thanks for the response. It works, except with one exception. If the
sheet is empty it selects first 4 columns instead of 2.

I had preiously tried this code:

Range("IV1").End(xlToLeft).Select
ActiveCell.Offset(0, 2).Select

It worked to paste data into the last 2 columns but it left colums A&B empty
if the sheet was empty (obviously).

I am not sure quite how to make a IF statement that would check the sheet to
see if A&B were empty on the first go round then select them. If not then
do the code you sent me. I suppose the code you sent me would work, but it
selects 4 colums when empty rather than 2. If I knew enough about the below
code I would make an attempt but not sure on how all of the code works.

"Don Guillett" wrote in message
...
try either

Sub selectlast2emptycolumns() 'two ways

'x = Cells(5, Columns.Count).End(xlToLeft).Address
'Range(x).Offset(0, 1).Resize(1, 2).EntireColumn.Select

x = Cells(5, Columns.Count).End(xlToLeft).Column + 1
Range(Cells(5, x), Cells(5, x + 1)).EntireColumn.Select

End Sub

--
Don Guillett
SalesAid Software

"Nathan Bell" wrote in message
...
All,

I am trying to determine the code to select the 2 last empty columns
in
Excel via VBA. The only code I can find is
Range("IV5").End(xlToLeft).Select the only reason for that is row 5
contains data and 1-4 don't, thus returning results further left than I
wanted when doing a paste.

Again I am trying to select the last 2 empty columns in a worksheet.
What
would be the most effective way to do this?

Regards,

Nathan

Nathan

If the sheet is truly empty, you should get an error because rFound will be
Nothing. I can't imagine a situation where it would select 4 columns. Can
you tell me how to reproduce that so I can see it.

Thanks,

--
Dick Kusleika
MVP - Excel
Excel Blog - Daily Dose of Excel
www.dicks-blog.com

Nathan Bell wrote:
Don,

Thanks for the response. It works, except with one exception. If the
sheet is empty it selects first 4 columns instead of 2.

I had preiously tried this code:

Range("IV1").End(xlToLeft).Select
ActiveCell.Offset(0, 2).Select

It worked to paste data into the last 2 columns but it left colums A&B
empty if the sheet was empty (obviously).

I am not sure quite how to make a IF statement that would check the sheet
to see if A&B were empty on the first go round then select them. If not
then do the code you sent me. I suppose the code you sent me would
work, but it selects 4 colums when empty rather than 2. If I knew enough
about the below code I would make an attempt but not sure on how all of
the code works.

"Don Guillett" wrote in message
...
try either

Sub selectlast2emptycolumns() 'two ways

'x = Cells(5, Columns.Count).End(xlToLeft).Address
'Range(x).Offset(0, 1).Resize(1, 2).EntireColumn.Select

x = Cells(5, Columns.Count).End(xlToLeft).Column + 1
Range(Cells(5, x), Cells(5, x + 1)).EntireColumn.Select

End Sub

--
Don Guillett
SalesAid Software

"Nathan Bell" wrote in message
...
All,

I am trying to determine the code to select the 2 last empty
columns in Excel via VBA. The only code I can find is
Range("IV5").End(xlToLeft).Select the only reason for that is row 5
contains data and 1-4 don't, thus returning results further left than I
wanted when doing a paste.

Again I am trying to select the last 2 empty columns in a worksheet.
What would be the most effective way to do this?

Regards,

Nathan

Dick,
He started out his post with "Don,", so I believe he was addressing Don's
approach rather than your approach when he stated it selected 4 columns.

--
Regards,
Tom Ogilvy

"Dick Kusleika" wrote in message
...
Nathan

If the sheet is truly empty, you should get an error because rFound will
be
Nothing. I can't imagine a situation where it would select 4 columns.
Can
you tell me how to reproduce that so I can see it.

Thanks,

--
Dick Kusleika
MVP - Excel
Excel Blog - Daily Dose of Excel
www.dicks-blog.com

Nathan Bell wrote:
Don,

Thanks for the response. It works, except with one exception. If
the
sheet is empty it selects first 4 columns instead of 2.

I had preiously tried this code:

Range("IV1").End(xlToLeft).Select
ActiveCell.Offset(0, 2).Select

It worked to paste data into the last 2 columns but it left colums A&B
empty if the sheet was empty (obviously).

I am not sure quite how to make a IF statement that would check the
sheet
to see if A&B were empty on the first go round then select them. If not
then do the code you sent me. I suppose the code you sent me would
work, but it selects 4 colums when empty rather than 2. If I knew
enough
about the below code I would make an attempt but not sure on how all of
the code works.

"Don Guillett" wrote in message
...
try either

Sub selectlast2emptycolumns() 'two ways

'x = Cells(5, Columns.Count).End(xlToLeft).Address
'Range(x).Offset(0, 1).Resize(1, 2).EntireColumn.Select

x = Cells(5, Columns.Count).End(xlToLeft).Column + 1
Range(Cells(5, x), Cells(5, x + 1)).EntireColumn.Select

End Sub

--
Don Guillett
SalesAid Software

"Nathan Bell" wrote in message
...
All,

I am trying to determine the code to select the 2 last empty
columns in Excel via VBA. The only code I can find is
Range("IV5").End(xlToLeft).Select the only reason for that is row 5
contains data and 1-4 don't, thus returning results further left than
I
wanted when doing a paste.

Again I am trying to select the last 2 empty columns in a worksheet.
What would be the most effective way to do this?

Regards,

Nathan

Range("IV1").End(xlToLeft).Select
if not isempty(selection) then
ActiveCell.Offset(0, 1).Select
End if

You only need to reference the upper left corner to paste. But if you want
to select the two columns then add

selection.Resize(,2).EntrireColumn.Select

But Dick's code addresses the original problem you posted about, finding the
rightmost used column regardless of which row is involved.

to account for an empty sheet you would do

Sub FindLastColumn()

Dim rFound As Range

Set rFound = Sheet1.Cells.Find("*", _
Sheet1.Cells(1, 1), , , xlByColumns, xlPrevious)
If rFound Is Nothing Then
Range("A:B").Select
Else
rFound.Offset(0, 1).Resize(1, 2).EntireColumn.Select
End If
End Sub


--
Regards,
Tom Ogilvy

"Nathan Bell" wrote in message
...
Don,

Thanks for the response. It works, except with one exception. If the
sheet is empty it selects first 4 columns instead of 2.

I had preiously tried this code:

Range("IV1").End(xlToLeft).Select
ActiveCell.Offset(0, 2).Select

It worked to paste data into the last 2 columns but it left colums A&B
empty
if the sheet was empty (obviously).

I am not sure quite how to make a IF statement that would check the sheet
to
see if A&B were empty on the first go round then select them. If not then
do the code you sent me. I suppose the code you sent me would work, but
it
selects 4 colums when empty rather than 2. If I knew enough about the
below
code I would make an attempt but not sure on how all of the code works.

"Don Guillett" wrote in message
...
try either

Sub selectlast2emptycolumns() 'two ways

'x = Cells(5, Columns.Count).End(xlToLeft).Address
'Range(x).Offset(0, 1).Resize(1, 2).EntireColumn.Select

x = Cells(5, Columns.Count).End(xlToLeft).Column + 1
Range(Cells(5, x), Cells(5, x + 1)).EntireColumn.Select

End Sub

--
Don Guillett
SalesAid Software

"Nathan Bell" wrote in message
...
All,

I am trying to determine the code to select the 2 last empty
columns
in
Excel via VBA. The only code I can find is
Range("IV5").End(xlToLeft).Select the only reason for that is row 5
contains data and 1-4 don't, thus returning results further left than
I
wanted when doing a paste.

Again I am trying to select the last 2 empty columns in a worksheet.
What
would be the most effective way to do this?

Regards,

Nathan

Did someone change it? I swear it said Dick the first time I read it. :)

Tom Ogilvy wrote:
Dick,
He started out his post with "Don,", so I believe he was addressing
Don's approach rather than your approach when he stated it selected 4
columns.


"Dick Kusleika" wrote in message
...
Nathan

If the sheet is truly empty, you should get an error because rFound
will be Nothing. I can't imagine a situation where it would select
4 columns. Can you tell me how to reproduce that so I can see it.

Thanks,

--
Dick Kusleika
MVP - Excel
Excel Blog - Daily Dose of Excel
www.dicks-blog.com

Nathan Bell wrote:
Don,

Thanks for the response. It works, except with one exception.
If the sheet is empty it selects first 4 columns instead of 2.

I had preiously tried this code:

Range("IV1").End(xlToLeft).Select
ActiveCell.Offset(0, 2).Select

It worked to paste data into the last 2 columns but it left colums
A&B empty if the sheet was empty (obviously).

I am not sure quite how to make a IF statement that would check the
sheet to see if A&B were empty on the first go round then select
them. If not then do the code you sent me. I suppose the code
you sent me would work, but it selects 4 colums when empty rather
than 2. If I knew enough about the below code I would make an
attempt but not sure on how all of the code works.

"Don Guillett" wrote in message
...
try either

Sub selectlast2emptycolumns() 'two ways

'x = Cells(5, Columns.Count).End(xlToLeft).Address
'Range(x).Offset(0, 1).Resize(1, 2).EntireColumn.Select

x = Cells(5, Columns.Count).End(xlToLeft).Column + 1
Range(Cells(5, x), Cells(5, x + 1)).EntireColumn.Select

End Sub

--
Don Guillett
SalesAid Software

"Nathan Bell" wrote in message
...
All,

I am trying to determine the code to select the 2 last empty
columns in Excel via VBA. The only code I can find is
Range("IV5").End(xlToLeft).Select the only reason for that is
row 5 contains data and 1-4 don't, thus returning results further
left than I wanted when doing a paste.

Again I am trying to select the last 2 empty columns in a
worksheet. What would be the most effective way to do this?

Regards,

Nathan

Tom changed it cuz he likes to pick on me.

--
Don Guillett
SalesAid Software

"Dick Kusleika" wrote in message
...
Did someone change it? I swear it said Dick the first time I read it. :)

Tom Ogilvy wrote:
Dick,
He started out his post with "Don,", so I believe he was addressing
Don's approach rather than your approach when he stated it selected 4
columns.


"Dick Kusleika" wrote in message
...
Nathan

If the sheet is truly empty, you should get an error because rFound
will be Nothing. I can't imagine a situation where it would select
4 columns. Can you tell me how to reproduce that so I can see it.

Thanks,

--
Dick Kusleika
MVP - Excel
Excel Blog - Daily Dose of Excel
www.dicks-blog.com

Nathan Bell wrote:
Don,

Thanks for the response. It works, except with one exception.
If the sheet is empty it selects first 4 columns instead of 2.

I had preiously tried this code:

Range("IV1").End(xlToLeft).Select
ActiveCell.Offset(0, 2).Select

It worked to paste data into the last 2 columns but it left colums
A&B empty if the sheet was empty (obviously).

I am not sure quite how to make a IF statement that would check the
sheet to see if A&B were empty on the first go round then select
them. If not then do the code you sent me. I suppose the code
you sent me would work, but it selects 4 colums when empty rather
than 2. If I knew enough about the below code I would make an
attempt but not sure on how all of the code works.

"Don Guillett" wrote in message
...
try either

Sub selectlast2emptycolumns() 'two ways

'x = Cells(5, Columns.Count).End(xlToLeft).Address
'Range(x).Offset(0, 1).Resize(1, 2).EntireColumn.Select

x = Cells(5, Columns.Count).End(xlToLeft).Column + 1
Range(Cells(5, x), Cells(5, x + 1)).EntireColumn.Select

End Sub

--
Don Guillett
SalesAid Software

"Nathan Bell" wrote in message
...
All,

I am trying to determine the code to select the 2 last empty
columns in Excel via VBA. The only code I can find is
Range("IV5").End(xlToLeft).Select the only reason for that is
row 5 contains data and 1-4 don't, thus returning results further
left than I wanted when doing a paste.

Again I am trying to select the last 2 empty columns in a
worksheet. What would be the most effective way to do this?

Regards,

Nathan