Need VBA Code to Copy Predefined Text for each one IPv4 Address that can be found in 1st Sheet - Remove Not Valid IPv4 data

[dvampoulis]

Hello Dear Excel Users,

I Am trying long time ago now to implement an XLSM file that Will create a script for creating LSPs for my LAB (Routers)

The XLSM view is as above

Actually in 1st XLSM Sheet Columns (A,B,C,D,E,F,G,H,I.J,K) are the data entered from another XLS and with colors (are being verified with conditional formating that are correct.

On Columns (M,N,O.P) are only the IPv4 addresses that will be used on the next part so to create a CLI script (for creating the LSPs in my LAB)

The case is that i nead for each one cell of Columns (M,O) that containts an IPv4 like (172.17.x.x) a predifined CLI code to be writen to another sheet of XLSM by adding this IPv4 Address. (See example bellow).

Example for 3xIP addresses found on Columns (M,O) it should create 7xLine code (Starting from MACRO_TE_TUNNEL_PATH_HOP NoCmd_TE_TUNNEL_PATH_HOP) and Ending in "ENDM" by adding also the IPv4 address at field "NEXT_HOP_IP = 172.17.X.X" (See example bellow).

The problem is that since on the 1st image as you can see some IPv4 addresses will be Null (No data) so VBA should delete 7x Lines CLI code for each one that will not find an IPv4 address (See example bellow - XLSM lines from 151 to 157 should be removed from VBA since no Valid IPv4 address found).

Could you please help me? I am really stacked!

Actually the needed VBA code should find from Sheet named as "XXX" the field that contains null IPv4 address "NEXT_HOP_IP = " delete it and also delete 3x Rows above and 3x Rows bellow

I found some other code (like the above) but i cannot make it work as i cannot filter and separate the wording "NEXT_HOP_IP = " with the "NEXT_HOP_IP = 172.17.x.x"

Your help is really appreciated.

Scenario1 (not match my needs - but is close relative):

Sub deleteRows()
'
' deleteRows Macro
'
' Keyboard Shortcut: Ctrl+s
'
Dim StartRange As String
Dim EndRange As String
Cells.Find(What:="NEXT_HOP_IP = ").Select
StartRange = ActiveCell.Address
EndRange = ActiveCell.Address & 4
ActiveSheet.Range(StartRange & ":" & EndRange).Select
Selection.Delete Shift:=xlUp

End Sub

Scenario2 (not match my needs - but is close relative):

Sub Delete_Rows()

Dim xRow As Integer
Dim strSearch As String

strSearch = "NEXT_HOP_IP = "
' Assuming Total is in column A as your picture shows, but you can configure to search anywhere

xRow = Range("A" & Rows.Count).End(xlUp).Row
Range("$A1:C" & xRow).Select

Selection.find(What:=strSearch, After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Select

Range(ActiveCell.Row & ":" & ActiveCell.Offset(-3, 0).Row).Select
Selection.Delete Shift:=xlUp

End Sub

Scenario3 (not match my needs - but is close relative):

Sub DeleteRows()
Dim last As Long
Dim i As Long
With ActiveSheet
last = .Cells(.Rows.Count, 1).End(xlUp).Row
For i = last To 1 Step -1 'Changed that from the original code!!!
If .Cells(i, 1).Value Like "NEXT_HOP_IP = 172*" Then
.Cells(i - 7, 1).Resize(6, 1).EntireRow.Delete
End If
Next i
End With
End Sub

Scenario4 (not match my needs - but is close relative):

Sub test()

Dim currentSht As Worksheet
Dim startCell As Range
Dim lastRow As Long
Dim lastCol As Long
Dim i as integer

Set currentSht = ActiveWorkbook.Sheets(1)

Set startCell = currentSht.Range("A1")
lastRow = startCell.SpecialCells(xlCellTypeLastCell).Row
lastCol = startCell.SpecialCells(xlCellTypeLastCell).Column

For i = lastRow To 1 Step - 1
If Cells(i, "B").Value <> "NEXT_HOP_IP = 172.17*" Then
Range(Cells(i, "B").Offset(1), Cells(i, "B").Offset(2)).EntireRow.Delete 'delete two below
Cells(i, "B").Offset(-1).EntireRow.Delete ' delete one above


End Sub

Scenario5 (not match my needs - but is close relative):

Sub Delete_Rows_Based_On_Criteria()

Dim newUpRow, newDownRow As Integer

Range("A1").Select
Cells.Find(What:="NEXT_HOP_IP = 172.17*", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False).Activate

newUpRow = ActiveCell.Row - 3
Rows(newUpRow).Delete shift:=xlUp

newDownRow = ActiveCell.Row + 3
Rows(newDownRow).Delete shift:=xlUp

End Sub

Scenario6 (not match my needs - but is close relative):

Sub DeleteSuccessfulRows()

Application.ScreenUpdating = False
Dim x
For x = Cells(Rows.Count, ActiveCell.Column).End(xlUp).Row To ActiveCell.Row Step -1
If Cells(x, 1) = "NEXT_HOP_IP = " Then 'If we find this text
Cells(x, 1).EntireRow.Delete 'Delete the entire row
Cells(x - 3, 1).EntireRow.Delete 'Delete the row 3 rows bellow it
Cells(x - 3, 1).EntireRow.Delete 'Delete the row 3 rows above it
x = x - 2
'Delete blank rows
ElseIf Cells(x, 1) = vbNullString Then Cells(x, 1).EntireRow.Delete
'Optional delete rows that contain "File looks like ..."
'ElseIf Cells(x, 1) = "File looks like it is not encrypted. Skipping ..." Then Cells(x, 1).EntireRow.Delete
'ElseIf Cells(x, 1) = "File could not be decrypted properly. Skipping ..." Then Cells(x, 1).EntireRow.Delete
End If
Next x
Application.ScreenUpdating = True

End Sub

------------


At your disposal,

Kind Regards,

Dimitris.

 

[Zot]

I think you can just use combination of Instr and also Len.
The length of characters in "NEXT_HOP_IP = " is always 14. Once you found NEXT_HOP_IP using Instr, then you can check the length of characters in cell using Len. If longer than 14, then it has IP number. This way you can differentiate between the two.

 

[jolivanes]

Read up on cross posting in your other post.

 

[dvampoulis]

Read up on cross posting in your other post.

Hello, I was not aware about cross posting. What is next? Please let me know.

 

[dvampoulis]

I think you can just use combination of Instr and also Len.
The length of characters in "NEXT_HOP_IP = " is always 14. Once you found NEXT_HOP_IP using Instr, then you can check the length of characters in cell using Len. If longer than 14, then it has IP number. This way you can differentiate between the two.

Thanks you for your reply, really appreciate it but i also have other text in that Sheet that containts 14 characters (But not include "NEXT_HOP_IP = ") Can you describe the way through ? (e.g an example of the VBA code) ?

Thanks & Regards,

 

[Zot]

Thanks you for your reply, really appreciate it but i also have other text in that Sheet that containts 14 characters (But not include "NEXT_HOP_IP = ") Can you describe the way through ? (e.g an example of the VBA code) ?

Thanks & Regards,

I meant something like this (just taking one of your possible sample code)

For x = Cells(Rows.Count, ActiveCell.Column).End(xlUp).Row To ActiveCell.Row Step -1
    If Not InStr(Cells(x, 1), "NEXT_HOP_IP = ") = 0 And Len(Cells(x, 1)) = 14 Then 'If we find this text
        Cells(x, 1).EntireRow.Delete 'Delete the entire row
        Cells(x - 3, 1).EntireRow.Delete 'Delete the row 3 rows bellow it
        Cells(x - 3, 1).EntireRow.Delete 'Delete the row 3 rows above it
        x = x - 2

 

[Zot]

Basically just put two conditions. It can be any combination like Find and Len. or anything else.

 

[Zot]

An example here adapted from your code. Note that Find will result in a range. So Set is used here. rngFound will be the location where the "NEXT_HOP_IP = " is found. If not found, rngFound is Nothing.
Code is not tested. Just modify to your need.

Sub Delete_Rows()

Dim xRow As Integer
Dim strSearch As String
Dim rngFound As Range

strSearch = "NEXT_HOP_IP = "
' Assuming Total is in column A as your picture shows, but you can configure to search anywhere

xRow = Range("A" & Rows.Count).End(xlUp).Row
Range("$A1:C" & xRow).Select

Set rngFound = Range("$A1:C" & xRow).Find(What:=strSearch, LookAt:=xlPart)

If Not rngFound Is Nothing Then
    Select Case Len(rngFound)
        Case 14               ' This is when only "NEXT_HOP_IP = " is found
            <your code here>
        Case Else             ' This is when only "NEXT_HOP_IP = " + IP address is found
            <your code here>
    End Select
End If

End Sub

 

[dvampoulis]

An example here adapted from your code. Note that Find will result in a range. So Set is used here. rngFound will be the location where the "NEXT_HOP_IP = " is found. If not found, rngFound is Nothing.
Code is not tested. Just modify to your need.

Sub Delete_Rows()

Dim xRow As Integer
Dim strSearch As String
Dim rngFound As Range

strSearch = "NEXT_HOP_IP = "
' Assuming Total is in column A as your picture shows, but you can configure to search anywhere

xRow = Range("A" & Rows.Count).End(xlUp).Row
Range("$A1:C" & xRow).Select

Set rngFound = Range("$A1:C" & xRow).Find(What:=strSearch, LookAt:=xlPart)

If Not rngFound Is Nothing Then
    Select Case Len(rngFound)
        Case 14               ' This is when only "NEXT_HOP_IP = " is found
            <your code here>
        Case Else             ' This is when only "NEXT_HOP_IP = " + IP address is found
            <your code here>
    End Select
End If

End Sub

Hello,

I Modified a little the code you send me and i finally using this one bellow: (but the problem is that are 20.000 rows and the delay is near 5-6 minutes). Is there any other more fast/smart way to achive the same result ?

Any help really appreciated.

Dim StartRange As String
Dim EndRange As String
Sheets("LSP Macro FWD").Select
For x = Cells(Rows.Count, ActiveCell.Column).End(xlUp).Row To ActiveCell.Row Step -1
    If Not InStr(Cells(x, 1), "NEXT_HOP_IP = ") = 0 And Len(Cells(x, 1)) = 14 Then 'If we find this text
        Cells(x, 1).EntireRow.Delete 'Delete the entire row
        Cells(x - 3, 1).Resize(6, 1).EntireRow.Delete  'Delete the row 3 rows bellow it

Kind Regards,

Dimitris,

 

[Zot]

To speed execution, you normally disabled screen updating and if the sheet has some formula doing calculation, you want to temporarily disable the calculation. Otherwise the macro will wait all these process to finish before continuing since those process has higher priority.

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

...your program here...


Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic

Say if you have program crashed in the middle of execution somewhere, the Calculation mode will end up in Manual mode. So, when you have formula, it will not execute to give answer. So, just go to File > Option > Formula and set to Automatic again (depend on your version). The Application.ScreenUpdating = True will automatically reset after program finished even if you did not put this line.