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

[Skibum346]

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

 

[DonkeyOte]

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]

You'll need to do a bit of recoding - ISERROR(SHEETS("SHEET1").RANGE("A" & CURRENT_ROW)

etc...

schoolboy error!

 

[Skibum346]

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]

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]

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]

[No message]

 

[Skibum346]

Ivan,

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

Any idea how to correct this?

 

[Skibum346]

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