HELP with .csv clean up

Jacko1996

New Member
Joined
May 29, 2015
Messages
21
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
 

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.
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
 
Upvote 0
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.
 
Upvote 0
"With .Cells(LR, "H")"
That is the line of code that is getting the error, above, what do I do to fix it?
 
Upvote 0
"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 :-
Code:
 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.
 
Upvote 0

Forum statistics

Threads
1,213,489
Messages
6,113,953
Members
448,535
Latest member
alrossman

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top