VBA quick help from a newbie

[Nathan Bell]

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 Kusleika]

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

 

[Don Guillett]

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

 

[Nathan Bell]

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.

 

[Dick Kusleika]

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,

 

[Tom Ogilvy]

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.

 

[Tom Ogilvy]

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

 

[Dick Kusleika]

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

 

[Don Guillett]

Tom changed it cuz he likes to pick on me.