CODEWITHSUNDEEP
excelvba
Dirk
Dirk

Reputation: 155

VBA - Remove values

I have a list of values representing geodata (longtitude and latitude). These values are placed within brackets. But in some cases, there is also a third value (altitude) and I want to delete these values.

[
8.621952,
50.127658
],
[
8.621888,
50.127703,
101.271873
],
[
8.621894,
50.128006,
101.436873
]

The VBA-Code for Excel (see beneath) works well but it does not remove the comma of the second value. But the comma has to be removed as well when the altitude value is removed.

I use the code as given beneath. But it does not remove the comma.

Sub Remove_altitude()

Dim Sht As Worksheet


Sheets("Sheet1").Activate
Set Sht = Sheets("Sheet1")


  For i = Cells(Rows.Count, 1).End(xlUp).Row To 3 Step -1
     If IsNumeric(Cells(i, 1)) Then
       If IsNumeric(Cells(i - 1, 1)) And IsNumeric(Cells(i - 2, 1)) Then Rows(i).Delete
     End If
  Next

  MsgBox "Task is finished!"

End Sub

Does anybody know how to adapt the code accordingly?

Upvotes: 0

Views: 50

Answers (1)

Scott Craner
Scott Craner

Reputation: 152450

Make the if multi line and use Replace:

Sub Remove_altitude()

Dim Sht As Worksheet


Set Sht = Sheets("Sheet1")

With Sht
  For i = .Cells(.Rows.Count, 1).End(xlUp).Row To 3 Step -1
     If IsNumeric(.Cells(i, 1)) Then
       If IsNumeric(.Cells(i - 1, 1)) And IsNumeric(.Cells(i - 2, 1)) Then 
           .Rows(i).Delete
           .Cells(i - 1, 1) = Replace(.Cells(i - 1, 1),",","")
       End If
     End If
  Next i
End With

  MsgBox "Task is finished!"

End Sub

One note, good practice is to use the sheet name or at least ActiveSheet as not to allow VBA to use the implicit active sheet.

Upvotes: 2

Related Questions