Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
equation that will filter out #N/A's
I currently have a string of items that looks like the following:
#N/A #N/A 1" Shaft Liner DensGlass Ultra 2'x12' #N/A #N/A #N/A 1/2" 4'x12' Regular #N/A #N/A 1/2" DensArmor Plus 4'x08' (paperless) #N/A #N/A 1/2" DensShield 32"x05' #N/A #N/A 1/2" Hardi-Backer 3'x05' #N/A 1/2" MR 4'x08' 1/2" MR 4'x12' #N/A #N/A #N/A 5/8" 4'x14' FC Type X #N/A #N/A 5/8" DensGlass Gold 4'x08' FC Type X #N/A #N/A #N/A 5/8" MR 4'x08' FC Type X #N/A 5/8" MR 4'x12' FC Type X #N/A #N/A I am trying to filter out the #N/A's, so that it will output on the same worksheet the following: 1" Shaft Liner DensGlass Ultra 2'x12' 1/2" 4'x12' Regular 1/2" DensArmor Plus 4'x08' (paperless) 1/2" DensShield 32"x05' 1/2" Hardi-Backer 3'x05' 1/2" MR 4'x08' 1/2" MR 4'x12' 5/8" 4'x14' FC Type X 5/8" DensGlass Gold 4'x08' FC Type X 5/8" MR 4'x08' FC Type X 5/8" MR 4'x12' FC Type X Any help would be appreciated. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
equation that will filter out #N/A's
Public Sub ProcessData()
Const TEST_COLUMN As String = "A" '<=== change to suit Dim i As Long Dim LastRow As Long With ActiveSheet LastRow = .Cells(.Rows.Count, TEST_COLUMN).End(xlUp).Row For i = LastRow to 1 Step -1 If IsError(.cells(i, TEST_COLUMN).Value) Then .Rows(i).Delete End If Next i End With End Sub -- __________________________________ HTH Bob "DFrank" wrote in message ... I currently have a string of items that looks like the following: #N/A #N/A 1" Shaft Liner DensGlass Ultra 2'x12' #N/A #N/A #N/A 1/2" 4'x12' Regular #N/A #N/A 1/2" DensArmor Plus 4'x08' (paperless) #N/A #N/A 1/2" DensShield 32"x05' #N/A #N/A 1/2" Hardi-Backer 3'x05' #N/A 1/2" MR 4'x08' 1/2" MR 4'x12' #N/A #N/A #N/A 5/8" 4'x14' FC Type X #N/A #N/A 5/8" DensGlass Gold 4'x08' FC Type X #N/A #N/A #N/A 5/8" MR 4'x08' FC Type X #N/A 5/8" MR 4'x12' FC Type X #N/A #N/A I am trying to filter out the #N/A's, so that it will output on the same worksheet the following: 1" Shaft Liner DensGlass Ultra 2'x12' 1/2" 4'x12' Regular 1/2" DensArmor Plus 4'x08' (paperless) 1/2" DensShield 32"x05' 1/2" Hardi-Backer 3'x05' 1/2" MR 4'x08' 1/2" MR 4'x12' 5/8" 4'x14' FC Type X 5/8" DensGlass Gold 4'x08' FC Type X 5/8" MR 4'x08' FC Type X 5/8" MR 4'x12' FC Type X Any help would be appreciated. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
equation that will filter out #N/A's
Thanks for the response. I assume this is VBA, so how do i input it? thanks.
"Bob Phillips" wrote: Public Sub ProcessData() Const TEST_COLUMN As String = "A" '<=== change to suit Dim i As Long Dim LastRow As Long With ActiveSheet LastRow = .Cells(.Rows.Count, TEST_COLUMN).End(xlUp).Row For i = LastRow to 1 Step -1 If IsError(.cells(i, TEST_COLUMN).Value) Then .Rows(i).Delete End If Next i End With End Sub -- __________________________________ HTH Bob "DFrank" wrote in message ... I currently have a string of items that looks like the following: #N/A #N/A 1" Shaft Liner DensGlass Ultra 2'x12' #N/A #N/A #N/A 1/2" 4'x12' Regular #N/A #N/A 1/2" DensArmor Plus 4'x08' (paperless) #N/A #N/A 1/2" DensShield 32"x05' #N/A #N/A 1/2" Hardi-Backer 3'x05' #N/A 1/2" MR 4'x08' 1/2" MR 4'x12' #N/A #N/A #N/A 5/8" 4'x14' FC Type X #N/A #N/A 5/8" DensGlass Gold 4'x08' FC Type X #N/A #N/A #N/A 5/8" MR 4'x08' FC Type X #N/A 5/8" MR 4'x12' FC Type X #N/A #N/A I am trying to filter out the #N/A's, so that it will output on the same worksheet the following: 1" Shaft Liner DensGlass Ultra 2'x12' 1/2" 4'x12' Regular 1/2" DensArmor Plus 4'x08' (paperless) 1/2" DensShield 32"x05' 1/2" Hardi-Backer 3'x05' 1/2" MR 4'x08' 1/2" MR 4'x12' 5/8" 4'x14' FC Type X 5/8" DensGlass Gold 4'x08' FC Type X 5/8" MR 4'x08' FC Type X 5/8" MR 4'x12' FC Type X Any help would be appreciated. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
equation that will filter out #N/A's
ok, i gnore the otehr question, i figured out where to put it in. But what am
i suppsed to change the "A" to? and how do i get the equation to recognize the data range i want filtered? "Bob Phillips" wrote: Public Sub ProcessData() Const TEST_COLUMN As String = "A" '<=== change to suit Dim i As Long Dim LastRow As Long With ActiveSheet LastRow = .Cells(.Rows.Count, TEST_COLUMN).End(xlUp).Row For i = LastRow to 1 Step -1 If IsError(.cells(i, TEST_COLUMN).Value) Then .Rows(i).Delete End If Next i End With End Sub -- __________________________________ HTH Bob "DFrank" wrote in message ... I currently have a string of items that looks like the following: #N/A #N/A 1" Shaft Liner DensGlass Ultra 2'x12' #N/A #N/A #N/A 1/2" 4'x12' Regular #N/A #N/A 1/2" DensArmor Plus 4'x08' (paperless) #N/A #N/A 1/2" DensShield 32"x05' #N/A #N/A 1/2" Hardi-Backer 3'x05' #N/A 1/2" MR 4'x08' 1/2" MR 4'x12' #N/A #N/A #N/A 5/8" 4'x14' FC Type X #N/A #N/A 5/8" DensGlass Gold 4'x08' FC Type X #N/A #N/A #N/A 5/8" MR 4'x08' FC Type X #N/A 5/8" MR 4'x12' FC Type X #N/A #N/A I am trying to filter out the #N/A's, so that it will output on the same worksheet the following: 1" Shaft Liner DensGlass Ultra 2'x12' 1/2" 4'x12' Regular 1/2" DensArmor Plus 4'x08' (paperless) 1/2" DensShield 32"x05' 1/2" Hardi-Backer 3'x05' 1/2" MR 4'x08' 1/2" MR 4'x12' 5/8" 4'x14' FC Type X 5/8" DensGlass Gold 4'x08' FC Type X 5/8" MR 4'x08' FC Type X 5/8" MR 4'x12' FC Type X Any help would be appreciated. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
equation that will filter out #N/A's
ITs obvious i have to make some modifications to your code, but im not
exactly sure where. i havent used VBA since 7th grade so icompletely forgot everything. if it helps, the data range is in column D, and i want it outputed in column F. Thanks. "Bob Phillips" wrote: Public Sub ProcessData() Const TEST_COLUMN As String = "A" '<=== change to suit Dim i As Long Dim LastRow As Long With ActiveSheet LastRow = .Cells(.Rows.Count, TEST_COLUMN).End(xlUp).Row For i = LastRow to 1 Step -1 If IsError(.cells(i, TEST_COLUMN).Value) Then .Rows(i).Delete End If Next i End With End Sub -- __________________________________ HTH Bob "DFrank" wrote in message ... I currently have a string of items that looks like the following: #N/A #N/A 1" Shaft Liner DensGlass Ultra 2'x12' #N/A #N/A #N/A 1/2" 4'x12' Regular #N/A #N/A 1/2" DensArmor Plus 4'x08' (paperless) #N/A #N/A 1/2" DensShield 32"x05' #N/A #N/A 1/2" Hardi-Backer 3'x05' #N/A 1/2" MR 4'x08' 1/2" MR 4'x12' #N/A #N/A #N/A 5/8" 4'x14' FC Type X #N/A #N/A 5/8" DensGlass Gold 4'x08' FC Type X #N/A #N/A #N/A 5/8" MR 4'x08' FC Type X #N/A 5/8" MR 4'x12' FC Type X #N/A #N/A I am trying to filter out the #N/A's, so that it will output on the same worksheet the following: 1" Shaft Liner DensGlass Ultra 2'x12' 1/2" 4'x12' Regular 1/2" DensArmor Plus 4'x08' (paperless) 1/2" DensShield 32"x05' 1/2" Hardi-Backer 3'x05' 1/2" MR 4'x08' 1/2" MR 4'x12' 5/8" 4'x14' FC Type X 5/8" DensGlass Gold 4'x08' FC Type X 5/8" MR 4'x08' FC Type X 5/8" MR 4'x12' FC Type X Any help would be appreciated. |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
equation that will filter out #N/A's
Change the "A"to "D" and run it, it will do everything in column D.
-- __________________________________ HTH Bob "DFrank" wrote in message ... ITs obvious i have to make some modifications to your code, but im not exactly sure where. i havent used VBA since 7th grade so icompletely forgot everything. if it helps, the data range is in column D, and i want it outputed in column F. Thanks. "Bob Phillips" wrote: Public Sub ProcessData() Const TEST_COLUMN As String = "A" '<=== change to suit Dim i As Long Dim LastRow As Long With ActiveSheet LastRow = .Cells(.Rows.Count, TEST_COLUMN).End(xlUp).Row For i = LastRow to 1 Step -1 If IsError(.cells(i, TEST_COLUMN).Value) Then .Rows(i).Delete End If Next i End With End Sub -- __________________________________ HTH Bob "DFrank" wrote in message ... I currently have a string of items that looks like the following: #N/A #N/A 1" Shaft Liner DensGlass Ultra 2'x12' #N/A #N/A #N/A 1/2" 4'x12' Regular #N/A #N/A 1/2" DensArmor Plus 4'x08' (paperless) #N/A #N/A 1/2" DensShield 32"x05' #N/A #N/A 1/2" Hardi-Backer 3'x05' #N/A 1/2" MR 4'x08' 1/2" MR 4'x12' #N/A #N/A #N/A 5/8" 4'x14' FC Type X #N/A #N/A 5/8" DensGlass Gold 4'x08' FC Type X #N/A #N/A #N/A 5/8" MR 4'x08' FC Type X #N/A 5/8" MR 4'x12' FC Type X #N/A #N/A I am trying to filter out the #N/A's, so that it will output on the same worksheet the following: 1" Shaft Liner DensGlass Ultra 2'x12' 1/2" 4'x12' Regular 1/2" DensArmor Plus 4'x08' (paperless) 1/2" DensShield 32"x05' 1/2" Hardi-Backer 3'x05' 1/2" MR 4'x08' 1/2" MR 4'x12' 5/8" 4'x14' FC Type X 5/8" DensGlass Gold 4'x08' FC Type X 5/8" MR 4'x08' FC Type X 5/8" MR 4'x12' FC Type X Any help would be appreciated. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Copy only visible cells after filter is applied/ sum after filter | Excel Worksheet Functions | |||
filter: how to print filter list options in dropdown box | Excel Discussion (Misc queries) | |||
Excel auto filter doesn't recoginize case - won't filter AA from A | Excel Discussion (Misc queries) | |||
Equation Editor- problem when editing an equation | Excel Discussion (Misc queries) | |||
"Criteria Range" in the "Data/Filter/Advanced Filter" to select Du | Excel Worksheet Functions |