delete calculated columns

L

lefty38

Board Regular
Joined
Oct 27, 2005
Messages
85
I have a borrowed VBA Code that will delete columns that = NR ( =IF((K1=0),"NR",(K2/K1)) )
problem is when the cells shift after deleting ==> the code seems to skip over other NR fields


I have attached the code and a small sample of the excel sheet
column range can be from H3 to many columns
calculations are turned off until this practical script runs (to save time)

thank you for any help

Code: 
Sub deleteNRcolumns()
Dim Rng As Range, cell As Range
'turn on Autocalc so the NR comlumns can be deleted
    Application.ScreenUpdating = True
       With Application
        .Calculation = xlAutomatic
        .MaxChange = 0.001

    Set Rng = Range("H3:CA3")
  
        For Each cell In Rng
            Select Case cell.Value
            Case Is = "NR"
                cell.EntireColumn.Delete
        End Select
    Next cell
    
      End With


[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]4
[/TD]
[TD][/TD]
[TD]4
[/TD]
[TD]1
[/TD]
[TD][/TD]
[TD]4
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]4
[/TD]
[TD][/TD]
[TD]4
[/TD]
[TD]1
[/TD]
[TD][/TD]
[TD]4
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]100%
[/TD]
[TD]NR
[/TD]
[TD]100%
[/TD]
[TD]100%
[/TD]
[TD]NR
[/TD]
[TD]100%
[/TD]
[TD]NR
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Country
[/TD]
[TD]State
[/TD]
[TD]City
[/TD]
[TD]Mgr
[/TD]
[TD]Emp
[/TD]
[TD]ID
[/TD]
[TD]Comp
[/TD]
[TD]crs1
[/TD]
[TD]crs2
[/TD]
[TD]crs3
[/TD]
[TD]crs4
[/TD]
[TD]crs5
[/TD]
[TD]crs6
[/TD]
[TD]crs7
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]fred
[/TD]
[TD]01
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]wilma
[/TD]
[TD]02
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]barny
[/TD]
[TD]03
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]bam
[/TD]
[TD]04
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
 

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.
Sort by date Sort by votes
What about if you don't use a loop at all...
Code: 
Sub DeleteColumnsEqualToNR()
  Rows(3).Replace "NR", "#N/A", xlWhole, , False, , False, False
  On Error Resume Next
  Rows(3).SpecialCells(xlConstants, xlErrors).EntireColumn.Delete
  On Error GoTo 0
End Sub
 
Upvote 0
Rick , thank you for the code
but for some reason it is not "finding" the "NR"

I referenced out the On error resume next -- it returns an error Run time error '1004' No cells were found

(I added the calculation feature so the row would calculate before deletion (so it can find a % or return an NR in the calculated field)
calculated field for this cell K3 is =IF((K1=0),"NR",(K2/K1)) all of the cells H3:CA3 have this formula (each formula has there own column reference number)

Code: 
Sub DeleteColumnsEqualToNR()
        Application.ScreenUpdating = True
        Application.Calculation = xlAutomatic
  Rows(3).Replace "NR", #N/A", xlWhole, , False, , False, False
  'On Error Resume Next
  Rows(3).SpecialCells(xlConstants, xlErrors).EntireColumn.Delete
  On Error GoTo 0
End Sub
 
Upvote 0
Try this mod to Rick's code
Code: 
Sub DeleteColumnsEqualToNR()
        Application.ScreenUpdating = True
        Application.Calculation = xlAutomatic
  Rows(3).Replace "NR", #N/A", [COLOR=#ff0000]xlPart[/COLOR], , False, , False, False
  'On Error Resume Next
  Rows(3).SpecialCells(xlConstants, xlErrors).EntireColumn.Delete
  On Error GoTo 0
End Sub
 
Upvote 0
Fluff said:
Try this mod to Rick's code
Code: 
Sub DeleteColumnsEqualToNR()
        Application.ScreenUpdating = True
        Application.Calculation = xlAutomatic
  Rows(3).Replace "NR", #N/A", [COLOR=#ff0000]xlPart[/COLOR], , False, , False, False
  'On Error Resume Next
  Rows(3).SpecialCells(xlConstants, xlErrors).EntireColumn.Delete
  On Error GoTo 0
End Sub
Click to expand...
That mod won't work Fluff. If NR is in the cell along with other characters, only the NR would be replace with #N/A, but the other characters would remain and those other characters would stop my SpecialCells from working. The way to change my code to handle other possible characters that may be in the cell along with the NR is like this...
Code: 
Sub DeleteColumnsEqualToNR()
        Application.ScreenUpdating = True
        Application.Calculation = xlAutomatic
  Rows(3).Replace "[B][COLOR="#FF0000"][SIZE=3]*[/SIZE][/COLOR][/B]NR[B][COLOR="#FF0000"][SIZE=3]*[/SIZE][/COLOR][/B]", #N/A", [COLOR="#FF0000"][B]xlWhole[/B][/COLOR], , False, , False, False
  'On Error Resume Next
  Rows(3).SpecialCells(xlConstants, xlErrors).EntireColumn.Delete
  On Error GoTo 0
End Sub
Note to lefty38... if the above code works, then you have other characters in the cells besides the letters NR.
 
Last edited:
Upvote 0
@Rick Rothstein
You're quite right (as normal), in that my mod does not work.
Unfortunately, as the NR is in the middle of a formula, your code will simply delete all the columns with that formula.
 
Upvote 0
Fluff said:
@Unfortunately, as the NR is in the middle of a formula...
Click to expand...
#2 ) either. Okay, looking more carefully at this, it appears Row 3 gets its NR value when either Row 1 0 or is blank (within the column range H:CA). Given that, I believe this macro should work...
Code: 
Sub DeleteColumnsEqualToNR()
  [H1:CA1] = [IF(H1:CA1=0,"",H1:CA1)]
  On Error Resume Next
  [H1:CA1].SpecialCells(xlBlanks).EntireColumn.Delete
  On Error GoTo 0
End Sub
" >@
Apparently, I have had a blind-spot on this fact as I did not take it into account in my first posting (Message #2 ) either. Okay, looking more carefully at this, it appears Row 3 gets its NR value when either Row 1 0 or is blank (within the column range H:CA). Given that, I believe this macro should work...
Code: 
Sub DeleteColumnsEqualToNR()
  [H1:CA1] = [IF(H1:CA1=0,"",H1:CA1)]
  On Error Resume Next
  [H1:CA1].SpecialCells(xlBlanks).EntireColumn.Delete
  On Error GoTo 0
End Sub
 
Upvote 0
Thanks All,
I will let you know how the code works, "Rick wrote", when i get to work tomorrow
 
Last edited:
Upvote 0
Ok Rick's final code in (message #7 ) does work - but the problem is now, row 5 is a filtered list
and his code turns the formulas in row 1 to hard coded numbers - in which when filtered the formulas do not work
(I hope I am making sense)

the cell in H1 is actually
=SUMPRODUCT(SUBTOTAL(3,OFFSET($F$7:$F$4001,ROW($F7:$F4001)-ROW($F7),,1)),--(ISNA(MATCH(I7:I4001,{"NR"},0))))

the cell in H2 is actually
=SUMPRODUCT(SUBTOTAL(3,OFFSET(H7:H4001,ROW(H7:H4001)-ROW(H7),0,1)),--(H7:H4001="Complete"))

the cell in H3 is actually
=IF((H1=0),"NR",(H2/H1))

Is there a way to turn the ROW H3:CA3 (or how many columns wide from H3)
Code: 
 [H3:CA3] = [IF(H3:CA3="NR","",H3:CA3)]
to a hard coded "NR" (or percentage) ==> then delete the "NR" column(s)
then re-insert the "=IF((H1=0),"NR",(H2/H1))" back into H3 and the remaining row 3 columns?


[TABLE="width: 758"]
<tbody>[TR]
[TD="class: xl66, width: 64, bgcolor: transparent"][/TD]
[TD="class: xl66, width: 64, bgcolor: transparent"][/TD]
[TD="class: xl66, width: 64, bgcolor: transparent"][/TD]
[TD="class: xl66, width: 64, bgcolor: transparent"][/TD]
[TD="class: xl66, width: 64, bgcolor: transparent"][/TD]
[TD="class: xl66, width: 64, bgcolor: transparent"][/TD]
[TD="class: xl66, width: 78, bgcolor: transparent"]Required
[/TD]
[TD="class: xl67, width: 94, bgcolor: white"]2
[/TD]
[TD="class: xl67, width: 72, bgcolor: white"]0
[/TD]
[TD="class: xl67, width: 86, bgcolor: white"]4
[/TD]
[TD="class: xl67, width: 83, bgcolor: white"]3
[/TD]
[TD="class: xl67, width: 70, bgcolor: white"]0
[/TD]
[TD="class: xl67, width: 74, bgcolor: white"]4
[/TD]
[TD="class: xl67, width: 71, bgcolor: white"]0
[/TD]
[/TR]
[TR]
[TD="class: xl68, width: 64, bgcolor: white"][/TD]
[TD="class: xl68, width: 64, bgcolor: white"][/TD]
[TD="class: xl68, width: 64, bgcolor: white"][/TD]
[TD="class: xl68, width: 64, bgcolor: white"][/TD]
[TD="class: xl68, width: 64, bgcolor: white"][/TD]
[TD="class: xl68, width: 64, bgcolor: white"][/TD]
[TD="class: xl69, width: 78, bgcolor: white"]Complete
[/TD]
[TD="class: xl67, width: 94, bgcolor: white"]2
[/TD]
[TD="class: xl67, width: 72, bgcolor: white"]0
[/TD]
[TD="class: xl67, width: 86, bgcolor: white"]4
[/TD]
[TD="class: xl67, width: 83, bgcolor: white"]3
[/TD]
[TD="class: xl67, width: 70, bgcolor: white"]0
[/TD]
[TD="class: xl67, width: 74, bgcolor: white"]3
[/TD]
[TD="class: xl67, width: 71, bgcolor: white"]0
[/TD]
[/TR]
[TR]
[TD="class: xl70, width: 64, bgcolor: white"][/TD]
[TD="class: xl70, width: 64, bgcolor: white"][/TD]
[TD="class: xl70, width: 64, bgcolor: white"][/TD]
[TD="class: xl70, width: 64, bgcolor: white"][/TD]
[TD="class: xl70, width: 64, bgcolor: white"][/TD]
[TD="class: xl70, width: 64, bgcolor: white"][/TD]
[TD="class: xl71, width: 78, bgcolor: white"][/TD]
[TD="class: xl72, width: 94, bgcolor: white"]100%
[/TD]
[TD="class: xl73, width: 72, bgcolor: white"]NR
[/TD]
[TD="class: xl72, width: 86, bgcolor: white"]100%
[/TD]
[TD="class: xl72, width: 83, bgcolor: white"]100%
[/TD]
[TD="class: xl73, width: 70, bgcolor: white"]NR
[/TD]
[TD="class: xl72, width: 74, bgcolor: white"]75%
[/TD]
[TD="class: xl73, width: 71, bgcolor: white"]NR
[/TD]
[/TR]
[TR]
[TD="class: xl74, width: 64, bgcolor: white"][/TD]
[TD="class: xl74, width: 64, bgcolor: white"][/TD]
[TD="class: xl74, width: 64, bgcolor: white"][/TD]
[TD="class: xl74, width: 64, bgcolor: white"][/TD]
[TD="class: xl74, width: 64, bgcolor: white"][/TD]
[TD="class: xl74, width: 64, bgcolor: white"][/TD]
[TD="class: xl75, width: 78, bgcolor: white"][/TD]
[TD="class: xl76, width: 94, bgcolor: white"][/TD]
[TD="class: xl76, width: 72, bgcolor: white"][/TD]
[TD="class: xl76, width: 86, bgcolor: white"][/TD]
[TD="class: xl76, width: 83, bgcolor: white"][/TD]
[TD="class: xl76, width: 70, bgcolor: white"][/TD]
[TD="class: xl76, width: 74, bgcolor: white"][/TD]
[TD="class: xl76, width: 71, bgcolor: white"][/TD]
[/TR]
[TR]
[TD="class: xl78, width: 64, bgcolor: white"]Country
[/TD]
[TD="class: xl78, width: 64, bgcolor: white"]State
[/TD]
[TD="class: xl78, width: 64, bgcolor: white"]City
[/TD]
[TD="class: xl78, width: 64, bgcolor: white"]Mgr
[/TD]
[TD="class: xl78, width: 64, bgcolor: white"]Emp
[/TD]
[TD="class: xl78, width: 64, bgcolor: white"]ID
[/TD]
[TD="class: xl78, width: 78, bgcolor: white"]Comp
[/TD]
[TD="class: xl78, width: 94, bgcolor: white"]crs1
[/TD]
[TD="class: xl78, width: 72, bgcolor: white"]crs2
[/TD]
[TD="class: xl78, width: 86, bgcolor: white"]crs3
[/TD]
[TD="class: xl78, width: 83, bgcolor: white"]crs4
[/TD]
[TD="class: xl78, width: 70, bgcolor: white"]crs5
[/TD]
[TD="class: xl78, width: 74, bgcolor: white"]crs6
[/TD]
[TD="class: xl78, width: 71, bgcolor: white"]crs7
[/TD]
[/TR]
[TR]
[TD="class: xl74, width: 64, bgcolor: white"][/TD]
[TD="class: xl74, width: 64, bgcolor: white"][/TD]
[TD="class: xl74, width: 64, bgcolor: white"][/TD]
[TD="class: xl74, width: 64, bgcolor: white"][/TD]
[TD="class: xl74, width: 64, bgcolor: white"]fred
[/TD]
[TD="class: xl74, width: 64, bgcolor: white"]1
[/TD]
[TD="class: xl77, width: 78, bgcolor: white"]100%
[/TD]
[TD="class: xl76, width: 94, bgcolor: white"]complete
[/TD]
[TD="class: xl76, width: 72, bgcolor: white"][/TD]
[TD="class: xl76, width: 86, bgcolor: white"]complete
[/TD]
[TD="class: xl76, width: 83, bgcolor: white"]complete
[/TD]
[TD="class: xl76, width: 70, bgcolor: white"][/TD]
[TD="class: xl76, width: 74, bgcolor: white"]complete
[/TD]
[TD="class: xl76, width: 71, bgcolor: white"][/TD]
[/TR]
[TR]
[TD="class: xl74, width: 64, bgcolor: white"][/TD]
[TD="class: xl74, width: 64, bgcolor: white"][/TD]
[TD="class: xl74, width: 64, bgcolor: white"][/TD]
[TD="class: xl74, width: 64, bgcolor: white"][/TD]
[TD="class: xl74, width: 64, bgcolor: white"]wilma
[/TD]
[TD="class: xl74, width: 64, bgcolor: white"]2
[/TD]
[TD="class: xl77, width: 78, bgcolor: white"]100%
[/TD]
[TD="class: xl76, width: 94, bgcolor: white"]NR
[/TD]
[TD="class: xl76, width: 72, bgcolor: white"][/TD]
[TD="class: xl76, width: 86, bgcolor: white"]complete
[/TD]
[TD="class: xl76, width: 83, bgcolor: white"]complete
[/TD]
[TD="class: xl76, width: 70, bgcolor: white"][/TD]
[TD="class: xl76, width: 74, bgcolor: white"]complete
[/TD]
[TD="class: xl76, width: 71, bgcolor: white"][/TD]
[/TR]
[TR]
[TD="class: xl74, width: 64, bgcolor: white"][/TD]
[TD="class: xl74, width: 64, bgcolor: white"][/TD]
[TD="class: xl74, width: 64, bgcolor: white"][/TD]
[TD="class: xl74, width: 64, bgcolor: white"][/TD]
[TD="class: xl74, width: 64, bgcolor: white"]barny
[/TD]
[TD="class: xl74, width: 64, bgcolor: white"]3
[/TD]
[TD="class: xl77, width: 78, bgcolor: white"]50%
[/TD]
[TD="class: xl76, width: 94, bgcolor: white"]NR
[/TD]
[TD="class: xl76, width: 72, bgcolor: white"][/TD]
[TD="class: xl76, width: 86, bgcolor: white"]complete
[/TD]
[TD="class: xl76, width: 83, bgcolor: white"]NR
[/TD]
[TD="class: xl76, width: 70, bgcolor: white"][/TD]
[TD="class: xl76, width: 74, bgcolor: white"][/TD]
[TD="class: xl76, width: 71, bgcolor: white"][/TD]
[/TR]
[TR]
[TD="class: xl74, width: 64, bgcolor: white"][/TD]
[TD="class: xl74, width: 64, bgcolor: white"][/TD]
[TD="class: xl74, width: 64, bgcolor: white"][/TD]
[TD="class: xl74, width: 64, bgcolor: white"][/TD]
[TD="class: xl74, width: 64, bgcolor: white"]bam
[/TD]
[TD="class: xl74, width: 64, bgcolor: white"]4
[/TD]
[TD="class: xl77, width: 78, bgcolor: white"]100%
[/TD]
[TD="class: xl76, width: 94, bgcolor: white"]complete
[/TD]
[TD="class: xl76, width: 72, bgcolor: white"][/TD]
[TD="class: xl76, width: 86, bgcolor: white"]complete
[/TD]
[TD="class: xl76, width: 83, bgcolor: white"]complete
[/TD]
[TD="class: xl76, width: 70, bgcolor: white"][/TD]
[TD="class: xl76, width: 74, bgcolor: white"]complete
[/TD]
[TD="class: xl76, width: 71, bgcolor: white"][/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
You must log in or register to reply here.

Forum statistics

Threads
1,223,903
Messages
6,175,284
Members
452,630
Latest member
OdubiYouth

Share this page

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
Back
Top