Help with a macro to identify rows showing the "#REF" error

Skibum346

New Member
Joined
Sep 9, 2002
Messages
7
I have a number of excel sheets set up as forms which have been issued to staff to fill in and return, which I then upload to Access.

Due to an oversight, there are a large number (300ish!) of lines with redundant formulas resulting in the #REF error.

I want to build a macro that will search ther sheet, identify the rows, select them and delete them.

Unfortunately I don't have the first idea of where to start!

Help?

Kindest

Frank

:)
 

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.

DonkeyOte

MrExcel MVP
Joined
Sep 6, 2002
Messages
9,123
say cells = Sheets("sheet1").range("a1:a10000").

SUB LOOP_ERROR

DATA = Sheets("sheet1"),range("a1:A10000")
CURRENT_ROW = 1

for each DATALINE in DATA

IF ISERROR("A" & CURRENT_ROW) then

Rows(CURRENT_ROW & ":" & CURRENT_ROW).Select
Selection.ClearContents

end if

CURRENT_ROW = CURRENT_ROW + 1

next DATALINE

Try that...
 

DonkeyOte

MrExcel MVP
Joined
Sep 6, 2002
Messages
9,123
You'll need to do a bit of recoding - ISERROR(SHEETS("SHEET1").RANGE("A" & CURRENT_ROW)

etc...

schoolboy error!
 

Skibum346

New Member
Joined
Sep 9, 2002
Messages
7
LASW10,

Just tried it having assumed where you have "sheet1" should be my sheet name. Getting an error on both the "say cells" line and the "DATA = " line.

The first returns a Compile error saying "Expected identifier or bracketed expression"; The second retrns "Expected end of statement" and highlights the comma after the sheet name and before "Range".

I'm a real VB novice so any help you could provide would be fantastic.

Kindest

Frank

On 2002-09-10 03:36, lasw10 wrote:
say cells = Sheets("sheet1").range("a1:a10000").

SUB LOOP_ERROR

DATA = Sheets("sheet1"),range("a1:A10000")
CURRENT_ROW = 1

for each DATALINE in DATA

IF ISERROR("A" & CURRENT_ROW) then

Rows(CURRENT_ROW & ":" & CURRENT_ROW).Select
Selection.ClearContents

end if

CURRENT_ROW = CURRENT_ROW + 1

next DATALINE

Try that...
 

DonkeyOte

MrExcel MVP
Joined
Sep 6, 2002
Messages
9,123

ADVERTISEMENT

Frank - as per my email...result of misunderstanding and typo - ignore say cells as this was me trying to be descriptive!

SUB LOOP_ERROR

DATA = SHEETS("sheet1").RANGE("a1:A10000")
CURRENT_ROW = 1

for each DATALINE in DATA

IF ISERROR(sheets("sheet1").range("a" & Current_row)) then

Rows(CURRENT_ROW & ":" & CURRENT_ROW).select
selection.delete Shift:= xlUp

END IF

CURRENT_ROW = CURRENT_ROW + 1

NEXT DATALINE
 

Ivan F Moala

MrExcel MVP
Joined
Feb 10, 2002
Messages
4,209
Perhaps something like this may help
NB: removes ALL Ref errors and NOt just =#Ref! Errors

<pre/>
Sub Remove_RefError()
Dim oCell As Range
Dim Rg As Range


Const RefErr As String = "#REF!"

On Error Resume Next
Set Rg = Selection.SpecialCells(xlFormulas, 23)
If Rg Is Nothing Then Exit Sub

With WorksheetFunction
For Each oCell In Rg
If InStr(1, oCell.Formula, RefErr) > 0 Then
oCell.Clear
End If
Next
End With
End Sub

</pre>
 

Skibum346

New Member
Joined
Sep 9, 2002
Messages
7
Ivan,

Your code works a treat except that it clears the cells, rather than remove the rows.

Any idea how to correct this?
 

Skibum346

New Member
Joined
Sep 9, 2002
Messages
7
SUCESS!!

Thought you would all like to know that I now have a solution. Thanks to the help here I was able to test and tweak the code supplied by LASW10.

I had to make the active columns "C" as that was the first column with the error ref. Also, I had to put a loop in as the origonal code moved cells up, then moved the active cell down one, leaving an error message row behind. Oh, and I had to declare the two variables at the start.

Thanks to all for the suggestions and help!

Here is the finished code: -

Sub LOOP_ERROR()

Dim DATA
Dim CURRENT_ROW


DATA = Sheets("tblFinalresult").Range("a1:A10000")
CURRENT_ROW = 1

For Each DATALINE In DATA

Do
If IsError(Sheets("tblFinalresult").Range("c" & CURRENT_ROW)) Then
Rows(CURRENT_ROW & ":" & CURRENT_ROW).Select
Selection.Delete Shift:=xlUp
End If
Loop Until IsError(Sheets("tblFinalresult").Range("c" & CURRENT_ROW)) = False


CURRENT_ROW = CURRENT_ROW + 1

Next DATALINE

End Sub
 

Forum statistics

Threads
1,144,508
Messages
5,724,767
Members
422,577
Latest member
kayth891

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
Top