find last cell with cell reference

[kevvukeka]

Hi All,

I have col reference stored in Last Column. How can I use till the last non blank cell range using the this col reference.

Below is my code: After removing the duplicates I need to reset the range to non blank rows. How to do it with cell 2 and col 64:


[VBA]
Sub test()
Dim ProviderName As String
Dim Lcolumn As Long, Lrow As Long
Dim rng1 As Range, cel2 As Variant
Range("AB2:AB" & Cells(Rows.Count, "AB").End(xlUp).Row).Select
Lcolumn = Cells(1, Columns.Count).End(xlToLeft).Column
Selection.Copy Cells.Item(2, Lcolumn + 1)
Lrow = Cells(2, Lcolumn + 1).SpecialCells(xlCellTypeLastCell).End(xlUp).Row
'Range(Cells(2, Lcolumn + 1), Cells(2, Lcolumn + 1)).End(xlUp).RemoveDuplicates Columns:=1, Header:=xlNo
'Range("BL2:BL" & Cells(Rows.Count, "BL").End(xlUp).Row).RemoveDuplicates Columns:=1, Header:=xlNo
Set rng1 = Range(Cells(2, Lcolumn + 1), Cells(Lrow, Lcolumn + 1))
rng1.Select
Selection.RemoveDuplicates Columns:=1, Header:=xlNo
[/VBA]

The code I not complete but after the last line, I need to reset rng1 variable to non blank cells in Lcolumn+1.

Kindly help.

Thanks & Regards,

[patel]

[VBA]Lcolumn = Cells(1, Columns.Count).End(xlToLeft).Column
Lrow = Cells(Rows.Count, Lcolumn).End(xlUp).Row[/VBA]

[kevvukeka]

Hi Patel,

I am sorry for my late reply...

I applied your code. but thing is before removing duplicates there 32260 rows, and now after removing duplicates when i try the
Lrow = Cells(Rows.Count, Lcolumn).End(xlUp).Rowit still selecting the 32260 rows even though there are just 14 rows with data.

can you suggest something...

[patel]

after removing duplicates you have to run both lines, not only one, if problems attach a sample file