Excel - VBA - Selecting Data in the columns

Question

WE have a code to clean and then copy excel data into a tab, where it accumulates. Columns A:H.

We need this data to have month in the next column (I). For all the new lines, meaning in the range (Ix:Ix) where the length is from last row of column I to last row of the newly pasted data.

The Code is below:

Sub Trial_Fix()

' Trial_Fix Macro
Dim LastRow As Long

' Deleting columns and adding columns of the raw data.
    Sheets("FP Data dump").Select
    Range("A:I,K:R,V:W").Select
    Selection.Delete Shift:=xlToLeft
    Columns("C:D").Select
    Selection.Insert Shift:=xlToRight
    Range("A1").Select
    Sheets("CF Data Dump").Select
    Range("A:C,E:E,H:H,J:J,M:O,Q:S,G:G").Select
    Selection.Delete Shift:=xlToLeft
    Range("A1").Select
    Sheets("Fuel Data Dump").Select
    Range("A:C,E:G,I:J,N:O,Q:Q,S:AC").Select
    Selection.Delete Shift:=xlToLeft
    Range("A1").Select

' Pasting CF Data

' Pasting FP + Data


' Pasting Fuel Data
    Sheets("Fuel Data Dump").Select
    LastRow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
    Range("A1:H" & LastRow).Select
    Selection.Copy
    Sheets("CLEAN FUEL DATA").Select
    Range("A" & Rows.Count).End(xlUp).Offset(1).Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
End Sub

First things first - [Avoid Select](http://stackoverflow.com/a/10717999/445425) — chris neilsen, Mar 18 '16 at 07:15

score 0 · Answer 1 · answered Mar 18 '16 at 10:44

If you continue to work with .Select, .Activate and the Application.Selection property, the following is all you require directly under the Range.PasteSpecial method command.

   Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, _
      SkipBlanks:=False, Transpose:=False
   With Selection
       With .Resize(.Rows.Count, 1).Offset(0, .Columns.Count)
           .Value = Date
           .NumberFormat = "mmmm"
       End With
   End With

However, using the methods and properties mentioned above is unreliable at best and evenly potentially capable of obfuscating your data. Consider the following rewrite that uses explicit referencing to address Range and Worksheet objects.

Sub Trial_Fix()
    ' Trial_Fix Macro
    Dim lrs As Long, lrd As Long
    Dim wsCFD As Worksheet

    With Worksheets("FP Data dump")
        .Range("A:I,K:R,V:W").Delete Shift:=xlToLeft
        .Columns("C:D").Insert Shift:=xlToRight

        ' Pasting FP + Data
        '?????????????

    End With

    With Worksheets("CF Data Dump")
        .Range("A:C,E:G,I:J,N:O,Q:Q,S:AC").Delete Shift:=xlToLeft

        ' Pasting CF Data
        '?????????????

    End With

    Set wsCFD = Worksheets("CLEAN FUEL DATA")
    With Worksheets("Fuel Data Dump")
        'get rid of unwanted columns
        .Range("A:C,E:E,G:H,J:J,M:O,Q:S").Delete Shift:=xlToLeft

        'last populated row in source
        lrs = .Cells(Rows.Count, 1).End(xlUp).Row

        'first blank cell in destination
        lrd = wsCFD.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row

        'direct value transfer is faster than copy/paste values and does not involve the clip board
        With .Range("A1:H" & lrs)
            wsCFD.Cells(lrd, 1).Resize(.Rows.Count, .Columns.Count) = .Value
            wsCFD.Cells(lrd, 1).Resize(.Rows.Count, 1).Offset(0, .Columns.Count) = Date
            wsCFD.Cells(lrd, 1).Resize(.Rows.Count, 1).Offset(0, .Columns.Count).NumberFormat = "mmmm"
        End With
    End With
End Sub

The above uses the With ... End With statement to reference .Parent worksheets. Anything inside the With ... End With gets its inherited parent reference by adding a period (aka full stop or .) as a prefix to each Range object.

Excel - VBA - Selecting Data in the columns

1 Answers1