Find last empty column in a sheet

Alex Piotto

Board Regular
Joined
Jul 5, 2016
Messages
82
Office Version
  1. 2007
Platform
  1. Windows
Hi, i am using the code below
VBA Code:
lastRow = Sheets("RESUMO").Cells(Rows.Count, "A").End(xlUp).Row
and
Code:
ThisWorkbook.Sheets("VENDAS").Range("DATAVENDA").Copy
ThisWorkbook.Sheets("RESUMO").Range("A" & lastRow + 1).PasteSpecial Paste:=xlPasteValues

to find the last empty row in a sheet and paste some values there. It works.

I now need to do the same in another sheet, this time finding the last empty colum and paste some value.

i am tryng to use this
Code:
lastCol = Sheets("DETTAGLI").Cells(Columns.Count, "A").End(xlUp).Column
with this
Code:
ThisWorkbook.Sheets("VENDAS").Range("C8:C127").Copy
ThisWorkbook.Sheets("DETTAGLI").Range("C8:C127" & lastCol + 1).PasteSpecial Paste:=xlPasteValues

but it paste the values always in the same column... what i am doing wrong?
 

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!
Which row will always have data, is it row 1?
 
Upvote 0
Hi Fluff, the example with the last row is ok.
Problem is with the second.
It paste the values in range C8:C127 but the part + 1 column does not works. It paste in the C8:C127 range every time.

I think this is wrong

VBA Code:
lastCol = Sheets("DETTAGLI").Cells(Columns.Count, "A").End(xlUp).Column
 
Last edited:
Upvote 0
I am no expert so please take anything I say with a large grain of salt. You have the code of:
VBA Code:
lastCol = Sheets("DETTAGLI").Cells(Columns.Count, "A").End(xlUp).Column

Since I think you are trying to count the columns, shouldn't that read something like:
VBA Code:
lastCol = Sheets("DETTAGLI").Cells(2, .Columns.Count).End(xlToLeft).Column     ' This would count the columns starting in row #2

Just a thought, but I'll follow this thread to see what the correct answer will be.

TotallyConfused
 
Upvote 0
You are totally right. Yesterday i do not know what i was thinking...
 
Upvote 0
Hello Alex

:) Don't feel bad. We all have days like that. It's called, 'being human'. I'm just glad we could help. Drop by anytime.

As a quick side note, you might take a minute to update your membership status to show the version of office you are using and also the platform. Some versions take different code.

TotallyConfused
 
Upvote 0
Hi!
Still in trouble...

VBA Code:
lastCol = Sheets("DETTAGLI").Cells(8, Columns.Count).End(xlToLeft).Column
        
        ThisWorkbook.Sheets("VENDAS").Range("C8:C127").Copy
        ThisWorkbook.Sheets("DETTAGLI").Range("C8:C127" & lastCol + 1).PasteSpecial Paste:=xlPasteValues

It will paste in the same place every time...

why?
 
Upvote 0
ThisWorkbook.Sheets("DETTAGLI").Range("C8:C127" & lastCol + 1).PasteSpecial Paste:=xlPasteValues
I think you will want to remove the 127 from what I highlighted in red above. Lets say lastCol evaluated to 99, then as written, your range would become this...

C8:C127&100

which evaluates to this...

C8&C12700

which I doubt is what you want.
 
Upvote 0
I think i got it... :))

VBA Code:
lastCol = Sheets("DETTAGLI").Cells(8, Columns.Count).End(xlToLeft).Column + 1
        
        ThisWorkbook.Sheets("VENDAS").Range("C8:C127").Copy
        ThisWorkbook.Sheets("DETTAGLI").Cells(8, lastCol).PasteSpecial Paste:=xlPasteValues
 
Upvote 0
I think you will want to remove the 127 from what I highlighted in red above. Lets say lastCol evaluated to 99, then as written, your range would become this...

C8:C127&100

which evaluates to this...

C8&C12700

which I doubt is what you want.
Hi Rick i am going to check it right now. Thanks
 
Upvote 0

Forum statistics

Threads
1,213,494
Messages
6,113,988
Members
448,538
Latest member
alex78

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top