Clear if "#N/A" and Find End of Range, Fill Blanks

[ryguy7272]

Simple problem seems to be turning into a complex problem. I want to clear
all cells that show #N/A.

Sub ClearRange()
Dim r As Range
Set r = Range("A2:A500")
For Each rr In r
If rr.Value = "#N/A" Then
rr.Offset(0, 1).Clear
End If
Next
End Sub

As an aside, I was trying to fill all blank cells with values from above
cells. It is easy to find the end of the range, but Excel seems to lose the
address of the last cell in the used range once I tell it to go to the first
cell in the used range (in my case it is A5)

Sub FindEnd()

Dim Rng As Range
Cells(Rows.Count, "A").End(xlUp).Select
ActiveCell.Select
Rng = ActiveCell

Selection.SpecialCells(xlCellTypeBlanks).Select
Range("A5:Rng").Select
Selection.FormulaR1C1 = "=R[-1]C"

End Sub


Any help with either macro would be greatly appreciated!!

Regards,
Ryan---

 

[StumpedAgain]

Try something like the following. I'm unsure why you did
"rr.Offset(1,0).Clear" when you're trying to clear rr if rr.Value = "#N/A".
Maybe I'm missing something. Also, I added a line to insert the above cell
value into the newly cleared cell.

Sub ClearRange()
Dim rr, r As Range
Set r = Range("A2:A500")
For Each rr In r
If rr.Value = "#N/A" Then
rr.Clear
rr.value = rr.offset(-1,0).value
End If
Next rr
End Sub

If you're looking to fill more blank cells, something like the following
should work.

Sub FillBlanks()
Dim rr, r As Range
Set r = Range("A2:A500")
For Each rr In r
If rr.Value = "" Then
rr.value = rr.offset(-1,0).value
End If
Next rr
End Sub

Hope this helps!

 

[ryguy7272]

For both macros i get:
I get a run time error '13'
Type Mismatch

Also, I need to find the used cell in Column A, because this list will
shrink grow each time data is pulled to create a report.

Any other ideas?

Thanks,
Ryan---

--
RyGuy

Try something like the following. I'm unsure why you did
"rr.Offset(1,0).Clear" when you're trying to clear rr if rr.Value = "#N/A".
Maybe I'm missing something. Also, I added a line to insert the above cell
value into the newly cleared cell.

Sub ClearRange()
Dim rr, r As Range
Set r = Range("A2:A500")
For Each rr In r
If rr.Value = "#N/A" Then
rr.Clear
rr.value = rr.offset(-1,0).value
End If
Next rr
End Sub

If you're looking to fill more blank cells, something like the following
should work.

Sub FillBlanks()
Dim rr, r As Range
Set r = Range("A2:A500")
For Each rr In r
If rr.Value = "" Then
rr.value = rr.offset(-1,0).value
End If
Next rr
End Sub

Hope this helps!
--
-SA

Simple problem seems to be turning into a complex problem. I want to clear
all cells that show #N/A.

Sub ClearRange()
Dim r As Range
Set r = Range("A2:A500")
For Each rr In r
If rr.Value = "#N/A" Then
rr.Offset(0, 1).Clear
End If
Next
End Sub

As an aside, I was trying to fill all blank cells with values from above
cells. It is easy to find the end of the range, but Excel seems to lose the
address of the last cell in the used range once I tell it to go to the first
cell in the used range (in my case it is A5)

Sub FindEnd()

Dim Rng As Range
Cells(Rows.Count, "A").End(xlUp).Select
ActiveCell.Select
Rng = ActiveCell

Selection.SpecialCells(xlCellTypeBlanks).Select
Range("A5:Rng").Select
Selection.FormulaR1C1 = "=R[-1]C"

End Sub


Any help with either macro would be greatly appreciated!!

Regards,
Ryan---

 

[Dave Peterson]

You could use:

If rr.Text = "#N/A" Then

or to test for any error in that cell:

If iserror(rr.Value) Then

For both macros i get:
I get a run time error '13'
Type Mismatch

Also, I need to find the used cell in Column A, because this list will
shrink grow each time data is pulled to create a report.

Any other ideas?

Thanks,
Ryan---

--
RyGuy

Try something like the following. I'm unsure why you did
"rr.Offset(1,0).Clear" when you're trying to clear rr if rr.Value = "#N/A".
Maybe I'm missing something. Also, I added a line to insert the above cell
value into the newly cleared cell.

Sub ClearRange()
Dim rr, r As Range
Set r = Range("A2:A500")
For Each rr In r
If rr.Value = "#N/A" Then
rr.Clear
rr.value = rr.offset(-1,0).value
End If
Next rr
End Sub

If you're looking to fill more blank cells, something like the following
should work.

Sub FillBlanks()
Dim rr, r As Range
Set r = Range("A2:A500")
For Each rr In r
If rr.Value = "" Then
rr.value = rr.offset(-1,0).value
End If
Next rr
End Sub

Hope this helps!
--
-SA

Simple problem seems to be turning into a complex problem. I want to clear
all cells that show #N/A.

Sub ClearRange()
Dim r As Range
Set r = Range("A2:A500")
For Each rr In r
If rr.Value = "#N/A" Then
rr.Offset(0, 1).Clear
End If
Next
End Sub

As an aside, I was trying to fill all blank cells with values from above
cells. It is easy to find the end of the range, but Excel seems to lose the
address of the last cell in the used range once I tell it to go to the first
cell in the used range (in my case it is A5)

Sub FindEnd()

Dim Rng As Range
Cells(Rows.Count, "A").End(xlUp).Select
ActiveCell.Select
Rng = ActiveCell

Selection.SpecialCells(xlCellTypeBlanks).Select
Range("A5:Rng").Select
Selection.FormulaR1C1 = "=R[-1]C"

End Sub


Any help with either macro would be greatly appreciated!!

Regards,
Ryan---

 

[ryguy7272]

Thanks for the code SA, and thanks, DP, for helping me to notice what I
should have noticed before I submitted my second post. Both macros are
working great!!

Regards,
Ryan---

--
RyGuy

You could use:

If rr.Text = "#N/A" Then

or to test for any error in that cell:

If iserror(rr.Value) Then

For both macros i get:
I get a run time error '13'
Type Mismatch

Also, I need to find the used cell in Column A, because this list will
shrink grow each time data is pulled to create a report.

Any other ideas?

Thanks,
Ryan---

--
RyGuy

Try something like the following. I'm unsure why you did
"rr.Offset(1,0).Clear" when you're trying to clear rr if rr.Value = "#N/A".
Maybe I'm missing something. Also, I added a line to insert the above cell
value into the newly cleared cell.

Sub ClearRange()
Dim rr, r As Range
Set r = Range("A2:A500")
For Each rr In r
If rr.Value = "#N/A" Then
rr.Clear
rr.value = rr.offset(-1,0).value
End If
Next rr
End Sub

If you're looking to fill more blank cells, something like the following
should work.

Sub FillBlanks()
Dim rr, r As Range
Set r = Range("A2:A500")
For Each rr In r
If rr.Value = "" Then
rr.value = rr.offset(-1,0).value
End If
Next rr
End Sub

Hope this helps!
--
-SA


:

Simple problem seems to be turning into a complex problem. I want to clear
all cells that show #N/A.

Sub ClearRange()
Dim r As Range
Set r = Range("A2:A500")
For Each rr In r
If rr.Value = "#N/A" Then
rr.Offset(0, 1).Clear
End If
Next
End Sub

As an aside, I was trying to fill all blank cells with values from above
cells. It is easy to find the end of the range, but Excel seems to lose the
address of the last cell in the used range once I tell it to go to the first
cell in the used range (in my case it is A5)

Sub FindEnd()

Dim Rng As Range
Cells(Rows.Count, "A").End(xlUp).Select
ActiveCell.Select
Rng = ActiveCell

Selection.SpecialCells(xlCellTypeBlanks).Select
Range("A5:Rng").Select
Selection.FormulaR1C1 = "=R[-1]C"

End Sub


Any help with either macro would be greatly appreciated!!

Regards,
Ryan---