Results 1 to 2 of 2
  1. #1
    New Lounger
    Join Date
    Apr 2012
    Posts
    23
    Thanks
    3
    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.

    ActiveSheet.Range("A2").Select
    ActiveSheet.Range("A2").CurrentRegion.Select

    Selection.Copy

    I have also used this code which eliminates the first row, but it too gives me the blanks.

    Range("A2").Select
    Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
    Selection.Copy

    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.

    Cells.Select
    ActiveSheet.Range("$A$1:$R$197").RemoveDuplicates Columns:=13, Header:= _
    xlYes

    Thank you for your assistance.
    Djsmith

  2. #2
    New Lounger
    Join Date
    Dec 2009
    Location
    Goulburn. NSW, Australia
    Posts
    20
    Thanks
    3
    Thanked 4 Times in 4 Posts
    You could try using the following code to select the range:

    ' Select the range to be used.
    Sub SelectRange()
    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
    End If
    End Sub

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •