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

R

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---
 
S

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!
 
R

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


StumpedAgain said:
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


ryguy7272 said:
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---
Click to expand...
Click to expand...
 
D

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

StumpedAgain said:
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


ryguy7272 said:
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---
Click to expand...
Click to expand...
Click to expand...
 
R

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


Dave Peterson said:
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

StumpedAgain said:
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---
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Eliminate blanks while copy row and transpose 11
clear all sheets code 4
Fin End of Used Range 2
Populating a range of empty cells with usernames 4
Find #N/A in column D 2
Clear a range of data 4
Fill down - adjust code 2
If #N/A in Col. AB, copy/paste Items in Col C. to New Sheet 8

Top