Trying to find last column with spaces in between data

[ian0886]

Hi all,

I'm fairly new to VBA and i'd need some help. I'm trying look for a code that finds the last blank cell after my data. Problem is within the data, there's are spaces in between. so what i'm trying to find is column J 1st row. Can anyone please help me?

	A	B	C	D	E	F	G	H	I	J
1
2	1st leg	2nd leg		1st leg	2nd leg		1st leg	2nd leg
3	12/6/2016	13/6/2016		13/6/2016	14/6/2016		14/6/2016	15/6/2016
4	12/6/2016	13/6/2016		13/6/2016	14/6/2016		14/6/2016	15/6/2016
5	12/6/2016	13/6/2016		13/6/2016	14/6/2016		14/6/2016	15/6/2016
6	12/6/2016	13/6/2016		13/6/2016	14/6/2016		14/6/2016	15/6/2016

<colgroup><col><col><col span="9"></colgroup><tbody>
</tbody>

 

[Ruddles]

Can you clarify... why is it row 1? Row 1 is before your data, isn't it?

Also, why is the last blank cell in column J? Why not K? Or I?

 

[ian0886]

Hi ruddles,
Yes you're right! It should be row 2 instead of row 1.

Secondly, basically Thia is an archive file. I'm trying to copy the dates, from another worksheet to this file. And becos it's for each day, there's a GAP in between each data set

 

[Tetra201]

Here are two VBA lines of code.

For the number of the very last used column in row 2:

Row2LastCol = Cells(2, Columns.Count).End(xlToLeft).Column

For the number of the very last used column in the entire sheet:

SheetLastCol = Columns(ActiveSheet.UsedRange.Column + ActiveSheet.UsedRange.Columns.Count - 1).Column

 

[ian0886]

ADVERTISEMENT

Hi Tetra,

The below is working but I've encounter a rather weird problem. The data spans some time ago and runs till somewhere column UA. When i run the macro once, somehow it got saved to column Z and AA. On running the second time, it seems to copy when i wanted it to be.

Row2LastCol = Cells(2, Columns.Count).End(xlToLeft).Column

 

[ian0886]

Hi,

Below is what i've done, but something pretty funny happened. When i run the macro on the 'date archive' tab it correct paste the data at the very last column. Somehow or rather when i just ran it on the 'working sheet' tab. it got paste in between the previous data set. is there something i can do?

I've even tried to add activate in each line so that its identifying correctly. It didn't work.

Dim Row2LastCol As LongRow2LastCol = Cells(1, Columns.Count).End(xlToLeft).Column
Worksheets("Working sheet").Range("a:c").EntireColumn.Copy
Worksheets("Date Archive").Cells(1, Row2LastCol + 2).PasteSpecial xlPasteAll
Worksheets("Working sheet").Range("c1:c100").Copy
Worksheets("Date Archive").Cells(1, Row2LastCol + 4).PasteSpecial xlPasteValues

 

[Ruddles]

I think rather than:-

Cells(1, Columns.Count).End(xlToLeft).Column

you need to code

Worksheets("Date Archive").Cells(1, Worksheets("Date Archive").Columns.Count).End(xlToLeft).Column

otherwise VBA doesn't know which sheet you're trying to find the last column of.

 

[ian0886]

thank you ruddles! it worked perfect