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

New Member
Joined
Feb 13, 2021
Messages
9
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2013
Platform
  1. Windows
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

XLSM Data Entry Page.jpg


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).

XLSM LSP Creation Page 1 (OK).jpg


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).

XLSM LSP Creation Page 1 (Not-OK #2).jpg


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.
 

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.

Zot

Well-known Member
Joined
Nov 26, 2020
Messages
1,170
Office Version
  1. 2016
Platform
  1. Windows
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

Well-known Member
Joined
Sep 5, 2004
Messages
1,712
Office Version
  1. 2013
  2. 2007
Platform
  1. Windows
Read up on cross posting in your other post.
 

dvampoulis

New Member
Joined
Feb 13, 2021
Messages
9
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2013
Platform
  1. Windows

ADVERTISEMENT

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

Well-known Member
Joined
Nov 26, 2020
Messages
1,170
Office Version
  1. 2016
Platform
  1. Windows
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)

VBA 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
 
Last edited:

Zot

Well-known Member
Joined
Nov 26, 2020
Messages
1,170
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

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

Zot

Well-known Member
Joined
Nov 26, 2020
Messages
1,170
Office Version
  1. 2016
Platform
  1. Windows
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.

VBA Code:
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

New Member
Joined
Feb 13, 2021
Messages
9
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2013
Platform
  1. Windows
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.

VBA Code:
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.

VBA Code:
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

Well-known Member
Joined
Nov 26, 2020
Messages
1,170
Office Version
  1. 2016
Platform
  1. Windows
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.
VBA Code:
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,129,803
Messages
5,638,444
Members
417,025
Latest member
MusterDuster

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top