Using autofilter / macro to remove #div/0 errors

farmertml

Board Regular
Joined
Jun 16, 2005
Messages
62
Hey guys, I have this macro which draws data from each sheet in my workbook.

Dim i As Integer
Dim wsname As String

For i = 2 To ThisWorkbook.Sheets.Count

Sheets(i).Select
wsname = Sheets(i).Name
Range("D1010:F1010,H1010").Copy
Sheets(1).Select
Range("C5").Select

If ActiveCell.Value <> "" Then

If ActiveCell.Offset(1, 0).Value <> "" Then

ActiveCell.End(xlDown).Select
ActiveCell.Offset(1, 0).Select
ActiveSheet.Paste

Else

ActiveCell.Offset(1, 0).Select
ActiveSheet.Paste

End If

Else

ActiveSheet.Paste

End If
ActiveCell.Offset(0, -1).Value = wsname

Next i

Range("B5:F153").Select
With Selection.Font
.Name = "Arial"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With

The one problem I am having is some of the pages won't have figures in the cells and will be putting a #div/0 error. Basically what I want to achieve is either hide the #div/0 errors using my autofilter or work out how to not copy the data if there is an error.

Thanks for any help
 

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.

farmertml

Board Regular
Joined
Jun 16, 2005
Messages
62
Don't you hate it when you answer your own question the moment you ask a question!!! All I was doing on my autofilter was forgetting to put the ! at the end of #div/0!

Hope this helps any other excel idiots like me !! :)
 

Watch MrExcel Video

Forum statistics

Threads
1,129,685
Messages
5,637,809
Members
416,983
Latest member
LessThanAverageUser

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