HELP with .csv clean up

[Jacko1996]

I have this code on my VBA to run a loop through my data so I can clean it up to leave just "Y" however i keep getting an error saying invalid next control variable reference. Not sure what to do. Thanks
Dim RowOne As Long
Dim RowTwo As Long
Dim LR As Long





With Sheet1
'We select the sheet so we can change the window view
.Select
'If you are in Page Break Preview Or Page Layout view go
'back to normal view, we do this for speed
ViewMode = ActiveWindow.View
ActiveWindow.View = xlNormalView
'Turn off Page Breaks, we do this for speed
.DisplayPageBreaks = False
'Set the first and last row to loop through
RowOne = .UsedRange.Cells(1).Row
RowTwo = .UsedRange.Rows(.UsedRange.Rows.Count).Row
'We loop from Lastrow to Firstrow (bottom to top)
For Lrow = lastrow To Firstrow Step -1
'We check the values in the A column in this example
With .Cells(LR, "H")
If Not IsError(.Value) Then
'Delete rows that are not quality checked, this includes "N" and blank, so only keep "Y"
If .Value <> "Y" And .Value <> "Quality" Then .EntireRow.Delete

End If
End With
Next LR
End With

End Sub

 

[ukmikeb]

Change this :-

Next LR
to
Next Lrow

hth

 

[Jacko1996]

Thank you for the quick response, however now im getting this area, Im not sure whats going on?
Application Defined or Object defined error.

'The below functions declare the name of the variables As Long.
'As Long allows the a number that can be used for a variable involving greater numbers than integers.


Dim RowOne As Long
Dim RowTwo As Long
Dim LR As Long






With Sheet2
'We select the sheet with the data on it.
.Select
'This allows the view to go back to normal viewing mode, if it is in any other view mode.
ViewMode = ActiveWindow.View
ActiveWindow.View = xlNormalView
'Allowing page breaks to not be displayed allows for better functionality and speed.
.DisplayPageBreaks = False
'Allow RowOne and RowTwo to loop through. (LOOP)
RowOne = .UsedRange.Cells(1).Row
RowTwo = .UsedRange.Rows(.UsedRange.Rows.Count).Row
'This allows the LOOP to go from bottom to top. (LOOP)
For LRow = lastrow To Firstrow Step -1
With .Cells(LR, "H") THIS HERE IS WHAT THE DEBUGGER IS HIGHLIGHTING
If Not IsError(.Value) Then
'All values that are not equal to "Y" are deleted.
If .Value <> "Y" And .Value <> "Quality" Then .EntireRow.Delete
End If
End With
Next LRow
End With

 

[ukmikeb]

Hi

At that point in the execution :-
LRow will equal lastrow
Lastrow, LR and Firstrow will be zero because none of them has been assigned a value.

If you want to keep quoting the code please place it within code tags, otherwise just quote the line of code and the error.

 

[Jacko1996]

"With .Cells(LR, "H")"
That is the line of code that is getting the error, above, what do I do to fix it?

 

[ukmikeb]

"With .Cells(LR, "H")"
That is the line of code that is getting the error, above, what do I do to fix it?

If you hover the cursor over the LR in that line you will see what value it is.

It will confirm what I said in post #4 - you can't reference a cell that isn't on the sheet.

I suggest you chnge this line :-

 For LRow = lastrow To Firstrow Step -1

to

 For LRow = RowTwo To RowOne Step -1

And for the line you mentioned in post #5, you need to change the variable LR to the variable that is the focus of your loop.

 

[Jacko1996]

Awesome that worked thanks a lot.