after my macros finishes through deleting "unwanted" cells & modifying "wanted" cells...column A has a list of cells w/the wanted data...any cell w/FE* has a corresponding "next" cell below...I want to merge the content of the two cells in to one cell...
Maybe if you see the whole picture...here is my full script:
Dim Filter As String, Title As String, FilterIndex As Integer, Filename As Variant, LastRow As Long
Dim rng As Range, c As Range, nb As Range, x As String, varLastRow, rw As Long
'***************************************************Function To Get Information From A File
Sub GetData4()
'***File types
Filter = "Excel Files (*.xls),*.xls," & _
"Text Files (*.txt),*.txt," & _
"All Files (*.*),*.*"
FilterIndex = 3 'Default Filter to *.*
Title = "SELECT FILE TO OPEN:" 'Caption
'*************************************Start Drive & Path
ChDir ("C:\")
With Application
'***Name selected File
Filename = .GetOpenFilename(Filter, FilterIndex, Title, ActiveWorkbook.Name)
End With
'***Exit With No Selection
If Filename = False Then
MsgBox "NO FILE WAS SELECTED!", vbExclamation
Exit Sub
End If
'***Open File & Extract Data
Workbooks.Open Filename
MsgBox Filename, vbInformation, "DATA EXTRACTED FROM:"
'************************************Modify Data In Cells
'***replace wanted content in cells
Application.ReplaceFormat.Clear
Application.ReplaceFormat.Font.Bold = True
Range("A1", Cells(Rows.Count, "A").End(xlUp)).Replace "description ", " ", xlPart, , False, , False, True
Range("A1", Cells(Rows.Count, "A").End(xlUp)).Replace "interface ", " ", xlPart, , False, , False, True
Range("A1", Cells(Rows.Count, "A").End(xlUp)).Replace "*ip address", " ", xlPart, , False, , False, True
Range("A1", Cells(Rows.Count, "A").End(xlUp)).Replace "GigabitEthernet", "GE", xlPart, , False, , False, True
Range("A1", Cells(Rows.Count, "A").End(xlUp)).Replace "FastEthernet", "FE", xlPart, , False, , False, True
Application.ReplaceFormat.Clear
'***bold wanted content in cells
For Each nb In Range("A1:A999")
If ActiveCell.Characters(10, 1).Font.Bold = False Then
ActiveCell.EntireRow.Delete
Else: ActiveCell.Offset(1, 0).Select
End If
Next nb
'***combine wanted content from an found data in each cell & merge the content to the next cell below
For Each nb In [A1:A555]
If nb.Value Like "FE*" Or nb.Value Like "GE*" Or nb.Value Like "Vlan*" Then
rw = rw + 1
Range("B" & rw).Value = nb.Value & " " & nb.Offset(1).Value
End If
Next nb
Columns("A:Z").EntireColumn.AutoFit
End Sub
