kevvukeka
06-07-2013, 03:27 AM
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:
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
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,
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:
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
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,