Results 1 to 2 of 2
2012-10-18, 05:59 #1
- Join Date
- Apr 2012
- Thanked 0 Times in 0 Posts
EXCEL 2010 VBA: Range("A2").CurrentRegion.Selectg
I have a worksheet where the range of cells change with each download of the data. The downloaded data gives me duplicates on some information. I am using excel to remove the duplicates on the contact numbers, which leaves blanks at the end of my data. When I Use the following code to select the range of data, I get the blank cells. I do not want to copy the header row (A1) and I do not want the blank cells.
I have also used this code which eliminates the first row, but it too gives me the blanks.
Is there a way I can eliminate the blank cells left from removing duplicates and not include the header row? I am also concerned that the range in the remove duplicate code may cause me problems, as well, when the number of records increase or decrease.
ActiveSheet.Range("$A$1:$R$197").RemoveDuplicates Columns:=13, Header:= _
Thank you for your assistance.
2012-10-18, 09:10 #2
- Join Date
- Dec 2009
- Goulburn. NSW, Australia
- Thanked 4 Times in 4 Posts
You could try using the following code to select the range:
' Select the range to be used.
Const cdblTopDataRow As Double = 2
Dim dblLastCol As Double
Dim dblLastRow As Double
dblLastRow = ActiveSheet.Cells(1, 1).CurrentRegion.Rows.Count
dblLastCol = ActiveSheet.Cells(1, 1).CurrentRegion.Columns.Count
If (dblLastRow >= cdblTopDataRow) Then
ActiveSheet.Range(ActiveSheet.Cells(cdblTopDataRow , 1), ActiveSheet.Cells(dblLastRow, dblLastCol)).Select