0

Using a macro, I'm joining 2 separate sets of columns with a colon using a simple function, like =A2&":"&B2

This works fine until it gets to the final row, where the macro is adding two additional cells each with just : in them. It looks like the macro auto-selected past the fields that contained data and joined the empty cell contents with the :.

I've reviewed the macro several times and don't see what might be causing it. Can someone spot check this for me?

Sub NetworkStatisticsFilter()
'

' NetworkStatisticsFilter Macro
' Format and Filter Data from Get-NetStatTCP to simplify port discovery
'

'
'Suppress alerts
'
    Application.DisplayAlerts = False

'
'Delete empty header rows that resulted from Get-NetStatTCP export
'
    Rows("1:1").Select
    Selection.Delete Shift:=xlUp
    Rows("2:2").Select
    Selection.Delete Shift:=xlUp

'
'Text-to-Columns, space-delimited
'
    Columns("A:A").Select
    Selection.TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
    TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=True, _
    Semicolon:=False, Comma:=False, Space:=True, Other:=False, FieldInfo _
    :=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1)), _
    TrailingMinusNumbers:=True

'
'Join LocalAddress & LocalPort with a ":" then copy results and replace both columns w/a paste-value
'
    Columns("C:C").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Columns("C:C").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Range("D1").Select
    ActiveCell.FormulaR1C1 = "SRC"
    Range("C2").Select
    ActiveCell.FormulaR1C1 = "=RC[-2]&"":""&RC[-1]"
    Range("C2").Select
    Selection.AutoFill Destination:=Range("C2:C77")
    Range("C2:C77").Select
    Selection.Copy
    Range("D2").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
    Columns("A:C").Select
    Range("C1").Activate
    Application.CutCopyMode = False
    Selection.Delete Shift:=xlToLeft

'
'Join RemoteAddress & RemotePort with a ":" then copy results and replace both columns w/a paste-value
'
    Columns("D:D").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Range("E1").Select
    ActiveCell.FormulaR1C1 = "DST"
    Range("D2").Select
    ActiveCell.FormulaR1C1 = "=RC[-2]&"":""&RC[-1]"
    Range("D2").Select
    Selection.AutoFill Destination:=Range("D2:D77")
    Range("D2:D77").Select
    Selection.Copy
    Range("E2").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
    Columns("B:D").Select
    Range("D1").Activate
    Application.CutCopyMode = False
    Selection.Delete Shift:=xlToLeft

'
'Auto-fit columns
'
    Columns("A:D").Select
    Columns("A:D").EntireColumn.AutoFit

'
'Auto-filter loopback address/port combinations and delete resulting rows
'
    Columns("A:A").Select
    Selection.AutoFilter
    ActiveSheet.Range("$A$1:$A$79").AutoFilter Field:=1, Criteria1:="=127.0.0.1*", Operator:=xlFilterValues
    ActiveSheet.UsedRange.Offset(1, 0).Resize(ActiveSheet.UsedRange.Rows.Count - 1).Rows.Delete
    ActiveSheet.ShowAllData

'
'Remove duplicate values in DST column
'
    Selection.AutoFilter
    ActiveSheet.Range("$A$1:$C$67").RemoveDuplicates Columns:=2, Header:=xlYes
    ActiveWindow.SmallScroll Down:=-9

'
'Allow Alerts
'
    Application.DisplayAlerts = True


End Sub
1
  • My initial thought would be that there's some extra blank spaces being imported in - There's is a difference between an empty and a blank cell in Excel. I'd consider writing the code to see how many lines of data you have, then only doing that many lines.
    – Selkie
    May 8, 2017 at 18:17

1 Answer 1

1

You are copying formula in columns using Range.AutoFill(). Thus, if the macro is creating formula in wrong cells that means the range you are providing is false. You are using Range("C2:C77"), the real one may be Range("C2:C76") for example.

You can find the last used row in a range (here C column) using this :

LastRow = Range("C" & Rows.Count).End(xlUp).Row

Furthermore when you delete empty headers you are :

(a) Deleting row 1

(b) Deleting row 2 which is row 3 before (a) was executed.

Is that on purpose ? If you wanted to delete first and second row you can do that :

Rows("1:2").EntireRow.Delete

EDIT:

As you indeed wanted to delete row 1 and 3 :

Rows(1).EntireRow.Delete
Rows(2).EntireRow.Delete
3
  • Thanks, I'll modify to use the LastRow help you included. Also, when the data is imported both rows 1 & 3 are empty due to the export from PowerShell. I delete Row 1 & shift up, which changes Row 3 to Row 2 and then delete the updated Row 2. I'll play with the Rows().EntireRow.Delete snippet and see if I can get it to skip row 2 and delete 1 & 3 simultaneously. May 8, 2017 at 19:55
  • Okay, so am I missing something to make a new paragraph and linebreak in the comment section here? May 8, 2017 at 20:01
  • If it's on purpose then you can't have one code line (as this is a "multiple" selection). See edit
    – Lich4r
    May 8, 2017 at 20:31

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .