Delete row in Excel

Question

how can i make it so this vba scripts deletes row containing a Percentage

Sub Macro2()

    Dim celltxt As String
    Dim a As Variant

    For Each i In Range(Cells(3, 2), Cells(160, 2))
        If i.Value > 1 Then
            i.Offset(0, 2).Value = i.Offset(0, 2).Value + 1
        End If

        If i.Value < 1 Then
            i.Offset(0, 2).Value = i.Offset(0, 2).Value + "NEI"
        End If

        If i.Value = "%" Then
            i.Offset(0, 2).Value = i.Offset(0, 3).Value + "joda"
        End If
    Next i

End Sub

Sub slettRadD()
    Range("D1:D160").Select
    Selection.ClearContents
End Sub

Sub SlettAlle()
    Range("A3:D160").Select
    Selection.ClearContents
End Sub

in this section

If i.Value = "%" Then
    i.Offset(0, 2).Value = i.Offset(0, 3).Value + "joda"
End If

i need it to delete the Horisontal row, or just delete the content in that cell. containing a percentage or percentage sign. just checked to see if i could even find it, but it does not seem work just adding a value in those colums.

score 2 · Answer 1 · answered Jun 02 '18 at 11:56

2

If i.Style = "Percent" Then
'Do Whatever here for example
  i.ClearContents
End If

answered Jun 02 '18 at 11:56

Raunak Thomas

1,393
1
12
28

score 1 · Answer 2 · answered Jun 02 '18 at 11:56

1

For formatted cells and if just cell contains (%)

If Right$(i.NumberFormat, 1) = "%"  Or i = "%" Then

answered Jun 02 '18 at 11:56

QHarr

83,427
12
54
101

What does that `$` in `Right$` do? Never seen that – LFB Jun 02 '18 at 12:11
It is a typed function. It is more efficient especially when doing string comparison in a loop. It is orders faster than just Right. It exists for many string functions e.g. Mid$ . As it for strings you need to handle potential nulls. See https://stackoverflow.com/a/37899863/6241235 – QHarr Jun 02 '18 at 12:20
Interesting! Thanks! – LFB Jun 02 '18 at 12:47

LFB · Answer 3 · 2018-06-02T13:14:25.883

0

Try

If i.Value = "%" Or Right$(i.NumberFormat,1) = "%" Then i.EntireRow.Delete

I think it will do the job. But it is going to possibly skip some rows, because after deletion, it won't compensate de increment in the loop.

If you need to delete ranges, try doing it from the bottom-up or right-left

This link has options: Reverse order of For Each loop

edited Jun 02 '18 at 13:14

answered Jun 02 '18 at 11:57

LFB

676
2
8
20

Delete row in Excel

3 Answers3