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?
 

Some videos you may like

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
48,520
Office Version
  1. 365
Platform
  1. Windows
Which row will always have data, is it row 1?
 

Alex Piotto

Board Regular
Joined
Jul 5, 2016
Messages
82
Office Version
  1. 2007
Platform
  1. Windows
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:

TotallyConfused

Board Regular
Joined
May 4, 2017
Messages
235
Office Version
  1. 365
Platform
  1. Windows
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
 

Alex Piotto

Board Regular
Joined
Jul 5, 2016
Messages
82
Office Version
  1. 2007
Platform
  1. Windows

ADVERTISEMENT

You are totally right. Yesterday i do not know what i was thinking...
 

TotallyConfused

Board Regular
Joined
May 4, 2017
Messages
235
Office Version
  1. 365
Platform
  1. Windows
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
 

Alex Piotto

Board Regular
Joined
Jul 5, 2016
Messages
82
Office Version
  1. 2007
Platform
  1. Windows

ADVERTISEMENT

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?
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,437
Office Version
  1. 2010
Platform
  1. Windows
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.
 

Alex Piotto

Board Regular
Joined
Jul 5, 2016
Messages
82
Office Version
  1. 2007
Platform
  1. Windows
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
 

Alex Piotto

Board Regular
Joined
Jul 5, 2016
Messages
82
Office Version
  1. 2007
Platform
  1. Windows
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
 

Watch MrExcel Video

Forum statistics

Threads
1,114,647
Messages
5,549,174
Members
410,903
Latest member
natesreich
Top