PDA

View Full Version : [SOLVED] One Row.Delete works, another doesn't



EirikDaude
09-01-2017, 01:14 AM
I have a worksheet in which I clean up some raw data. In it there are a few different loops which are supposed to delete rows, e.g.


Sub fjern_ugyldige_verdiar(sluttpunkt As Long, ws As Worksheet)
Dim i As Long

For i = sluttpunkt To 1 Step -1
If Not IsNumeric(ws.Range("B" & i)) Then
ws.Rows(i).Delete
End If
Next i
End Sub
and

Sub rydd_i_månadsmålingar()
Dim i As Long, j As Long

With Månads
...
For j = i To 1 Step -1
If .Range("B" & i) = 0 Then
.Rows(i).Delete
End If
Next j
End With
End Sub

Now, the problem is that the first one actually deletes the rows while the latter doesn't. I have checked that I loops over the correct range, and that the sub enters the if-statement - both of these are the case, so I am stumped why the rows aren't deleted from the sheet. Do any of you guys have some input on what might be the problem here.

The raw data is pasted into the sheet "Enkeltmålingar" and the macro is then run by clicking the button near the top.

20220

offthelip
09-01-2017, 03:51 AM
looking at your code firstly what is Manads ? it isn't defined in the English version of eXCEL, is it defined?

secondly you dim i as long, which means it is exclusive to this subroutine, but then you don't set it to anything so it will default to zero. so your loop is going from zero to 1 with a step of -1 one so it does nothing, ie. you never get into the the if statemen

EirikDaude
09-01-2017, 04:36 AM
"Månad" is the codename of a worksheet, as you can see in project explorer window:

20225
And I'm afraid I can't see any instances of i not being initialized, can you tell me what sub you see this in?

offthelip
09-01-2017, 06:07 AM
I downloaded your workbook and it is apparent that there is more code in the sub that you posted , I hadn't realised the significance of the dots. Anyway I tried running rydd_i_manadsmalingar and it deletes rows on my computer quite happily.

Aflatoon
09-01-2017, 07:11 AM
You aren't using j in the loop. It should be:


For j = i To 1 Step -1
If .Range("B" & j) = 0 Then
.Rows(j).Delete
End If
Next j

EirikDaude
09-04-2017, 12:23 AM
You aren't using j in the loop. It should be:


For j = i To 1 Step -1
If .Range("B" & j) = 0 Then
.Rows(j).Delete
End If
Next j


Ugh, so much for not using descriptive variable names. Thanks a ton for your help.