Hello,
I'm trying to remove and change certain values from a dataset which has been filtered.
As the size of the data-set is quite big, I want to narrow down the FOR range in order to make the vba more efficient.
I found some threads suggesting the "For Each" and with a range In specialcells "type visible" - but I seem to have issiues to get this to work.
In addition, As I'm deleting and moving up rows from the data-set, I'm afraid that the code will skip the just moved-up row if i leave the code "as is".
This is what my code looked like, but as it checks every i, its very inefficient - even though it does work perfectly.
I suppose something like this is what I need, allthough I'm not able to make it work.. Does anyone have any ideas?
Thank you for your time.
Sincerely
Kasper C
I'm trying to remove and change certain values from a dataset which has been filtered.
As the size of the data-set is quite big, I want to narrow down the FOR range in order to make the vba more efficient.
I found some threads suggesting the "For Each" and with a range In specialcells "type visible" - but I seem to have issiues to get this to work.
In addition, As I'm deleting and moving up rows from the data-set, I'm afraid that the code will skip the just moved-up row if i leave the code "as is".
This is what my code looked like, but as it checks every i, its very inefficient - even though it does work perfectly.
VBA Code:
For i = LastRow To 2 Step -1
If Left(ws.Cells(i, 1), 1) = "2" Then
If Left(ws.Cells(i, 1), 4) = "2000" And Len(ws.Cells(i, 1)) > 4 _
Then
ws.Cells(i, 1).EntireRow.Delete Shift:=xlUp
End If
If Left(ws.Cells(i, 1), 2) = "23" And Len(ws.Cells(i, 1)) > 4 And Len(ws.Cells(i, 1)) < 13 _
Then
ws2.Range("A2").Value = ws.Cells(i, 1).Value & "0000"
ws.Cells(i, 1).Value = ws2.Range("H2").Value
End If
If Left(ws.Cells(i, 1), 2) = "20" And Len(ws.Cells(i, 1)) > 4 And Len(ws.Cells(i, 1)) < 13 _
Then
ws2.Range("A2").Value = ws.Cells(i, 1).Value & "0000"
ws.Cells(i, 1).Value = ws2.Range("H2").Value
End If
End If
Next i
I suppose something like this is what I need, allthough I'm not able to make it work.. Does anyone have any ideas?
VBA Code:
Dim i As Range
For Each i In ws.Range("A2:A" & LastRow).SpecialCells(xlCellTypeVisible)
If Left(i, 1) = "2" And Not Left(i, 2) = "29" Then
If Left(i, 4) = "2000" And Len(i) > 4 _
Then
i.EntireRow.Delete Shift:=xlUp
End If
If Left(i, 2) = "23" And Len(i.Value) > 4 And Len(i.Value) < 13 _
Then
ws2.Range("A2").Value = i.Value & "0000"
i.Value = ws2.Range("H2").Value
End If
If Left(i, 2) = "20" And Len(i.Value) > 4 And Len(i.Value) < 13 _
Then
ws2.Range("A2").Value = i.Value & "0000"
i.Value = ws2.Range("H2").Value
End If
End If
Next i
Thank you for your time.
Sincerely
Kasper C