Run macro, lose Named Ranges

E

ElsiE93

New Member
Joined
Dec 5, 2018
Messages
17
Hi guys,

I have a macro which does exactly what I need it to.


The macro is run and then there is another summary tab whichuses formulas with Named Ranges to calculate values.


There are 3 Named Ranges: (on ZTDA Tracker tab)



Units:K:K


Pickers:I:I


Location:H:H


When I run my macro, the named ranges that I have set up arelost and the formulas that use them end up having Reference errors. Is there a wayto set named ranges to run on specific sheets so that when the macro I run, thenamed ranges are kept and the formulas can still calculate?





I can manually change them but it is far more efficient forthe named ranges to remain.


Code: 
Sub CompleteMacro()

' ZTDA_Sort_X_51 Macr0
'
'
Sheets("ZTDA Tracker").Select
Cells.Select
Selection.EntireColumn.Hidden = False
Rows("1:4").Select
    Selection.Delete Shift:=xlUp
Columns("A:C").Select
    Selection.Delete Shift:=xlToLeft
Columns("B:H").Select
    Selection.Delete Shift:=xlToLeft
Range("H:H,J:J").Select
Range("J1").Activate
    Selection.Delete Shift:=xlToLeft
Rows("1:1").Select
    Selection.AutoFilter
ActiveSheet.Range("$A$1:$Y$277").AutoFilter Field:=8, Criteria1:="51"
ActiveSheet.Range("$A$1:$Y$277").AutoFilter Field:=10, Criteria1:="X"
'Delete Hidden
For lp = 256 To 1 Step -1
If Columns(lp).EntireColumn.Hidden = True Then Columns(lp).EntireColumn.Delete Else
Next
For lp = 65536 To 1 Step -1
If Rows(lp).EntireRow.Hidden = True Then Rows(lp).EntireRow.Delete Else
Next
' Compass_Remove_2 Macro
'
'
Columns("C:C").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Range("C1").Value = "Compass?"
Range("C2", Range("B" & Rows.Count).End(xlUp).Offset(, 1)).FormulaR1C1 = "=COUNTIF(Mids!R2C3:R804C3,RC2)>0"
With Range("$A$1:$Z$1")
   .AutoFilter Field:=3, Criteria1:="TRUE"
   .Parent.AutoFilter.Range.Offset(1).EntireRow.Delete
   .Parent.AutoFilterMode = False
End With
Sheets("Overs").Select
' Overs_Sort Macro
'
Dim rng As Range
'
    Range("1:11,14:14").Select
    Range("A14").Activate
    Selection.Delete Shift:=xlUp
    Columns("A:I").Select
    Selection.AutoFilter
    ActiveSheet.Range("$A$1:$I$444").AutoFilter Field:=5, Criteria1:=Array( _
        "BP10-AMB", "CY10-B/LINE", "GT10-B/LINE", "GT10-NF"), Operator:=xlFilterValues
    ActiveSheet.Range("$A$1:$I$444").AutoFilter Field:=6, Criteria1:="OVER"
    
'Delete Hidden Overs
For lp = 1200 To 1 Step -1
If Rows(lp).EntireRow.Hidden = True Then Rows(lp).EntireRow.Delete Else
Next
Sheets("ZTDA Tracker").Select
' Overs_Match_ZTDA Macro
'
'
Columns("G:G").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Range("G1").Value = "Overs Match"
Range("G2", Range("F" & Rows.Count).End(xlUp).Offset(, 1)).FormulaR1C1 = "=COUNTIF(Overs!C1,RC[-1])>0"
With Range("$A$1:$Z$1")
    .AutoFilter Field:=7, Criteria1:="FALSE"
    .Parent.AutoFilter.Range.Offset(1).EntireRow.Delete
   .Parent.AutoFilterMode = False
End With
' Location_Overs_Vlookup Macro
'
'
    Columns("H:H").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Range("H1").Value = "Location"
    Range("H2", Range("G" & Rows.Count).End(xlUp).Offset(, 1)).FormulaR1C1 = "=VLOOKUP(RC[-2],Overs!C[-7]:C[-2],5,FALSE)"
    
' Picker_Match Macro
'
'
    Columns("I:I").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Range("I1").Value = "Picker"
    Range("I2", Range("H" & Rows.Count).End(xlUp).Offset(, 1)).FormulaR1C1 = "=VLOOKUP(RC[-8],IF(COUNTIF(RC[-1],""*CY10*""),'Input Corby Picker Data'!C1:C6,'Input GT BP Picker Data'!C1:C6),3,FALSE)"
End Sub
 
Last edited by a moderator:

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.
Sort by date Sort by votes
ElsiE93 said:
Hi guys,

I have a macro which does exactly what I need it to.


The macro is run and then there is another summary tab whichuses formulas with Named Ranges to calculate values.


There are 3 Named Ranges: (on ZTDA Tracker tab)



Units:K:K


Pickers:I:I


Location:H:H


When I run my macro, the named ranges that I have set up arelost and the formulas that use them end up having Reference errors. Is there a wayto set named ranges to run on specific sheets so that when the macro I run, thenamed ranges are kept and the formulas can still calculate?





I can manually change them but it is far more efficient forthe named ranges to remain.


Code: 
Sub CompleteMacro()

' ZTDA_Sort_X_51 Macr0
'
'
Sheets("ZTDA Tracker").Select
Cells.Select
Selection.EntireColumn.Hidden = False
Rows("1:4").Select
    Selection.Delete Shift:=xlUp
Columns("A:C").Select
    Selection.Delete Shift:=xlToLeft
Columns("B:H").Select
    Selection.Delete Shift:=xlToLeft
Range("H:H,J:J").Select
Range("J1").Activate
    Selection.Delete Shift:=xlToLeft
Rows("1:1").Select
    Selection.AutoFilter
ActiveSheet.Range("$A$1:$Y$277").AutoFilter Field:=8, Criteria1:="51"
ActiveSheet.Range("$A$1:$Y$277").AutoFilter Field:=10, Criteria1:="X"
'Delete Hidden
For lp = 256 To 1 Step -1
If Columns(lp).EntireColumn.Hidden = True Then Columns(lp).EntireColumn.Delete Else
Next
For lp = 65536 To 1 Step -1
If Rows(lp).EntireRow.Hidden = True Then Rows(lp).EntireRow.Delete Else
Next
' Compass_Remove_2 Macro
'
'
Columns("C:C").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Range("C1").Value = "Compass?"
Range("C2", Range("B" & Rows.Count).End(xlUp).Offset(, 1)).FormulaR1C1 = "=COUNTIF(Mids!R2C3:R804C3,RC2)>0"
With Range("$A$1:$Z$1")
   .AutoFilter Field:=3, Criteria1:="TRUE"
   .Parent.AutoFilter.Range.Offset(1).EntireRow.Delete
   .Parent.AutoFilterMode = False
End With
Sheets("Overs").Select
' Overs_Sort Macro
'
Dim rng As Range
'
    Range("1:11,14:14").Select
    Range("A14").Activate
    Selection.Delete Shift:=xlUp
    Columns("A:I").Select
    Selection.AutoFilter
    ActiveSheet.Range("$A$1:$I$444").AutoFilter Field:=5, Criteria1:=Array( _
        "BP10-AMB", "CY10-B/LINE", "GT10-B/LINE", "GT10-NF"), Operator:=xlFilterValues
    ActiveSheet.Range("$A$1:$I$444").AutoFilter Field:=6, Criteria1:="OVER"
    
'Delete Hidden Overs
For lp = 1200 To 1 Step -1
If Rows(lp).EntireRow.Hidden = True Then Rows(lp).EntireRow.Delete Else
Next
Sheets("ZTDA Tracker").Select
' Overs_Match_ZTDA Macro
'
'
Columns("G:G").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Range("G1").Value = "Overs Match"
Range("G2", Range("F" & Rows.Count).End(xlUp).Offset(, 1)).FormulaR1C1 = "=COUNTIF(Overs!C1,RC[-1])>0"
With Range("$A$1:$Z$1")
    .AutoFilter Field:=7, Criteria1:="FALSE"
    .Parent.AutoFilter.Range.Offset(1).EntireRow.Delete
   .Parent.AutoFilterMode = False
End With
' Location_Overs_Vlookup Macro
'
'
    Columns("H:H").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Range("H1").Value = "Location"
    Range("H2", Range("G" & Rows.Count).End(xlUp).Offset(, 1)).FormulaR1C1 = "=VLOOKUP(RC[-2],Overs!C[-7]:C[-2],5,FALSE)"
    
' Picker_Match Macro
'
'
    Columns("I:I").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Range("I1").Value = "Picker"
    Range("I2", Range("H" & Rows.Count).End(xlUp).Offset(, 1)).FormulaR1C1 = "=VLOOKUP(RC[-8],IF(COUNTIF(RC[-1],""*CY10*""),'Input Corby Picker Data'!C1:C6,'Input GT BP Picker Data'!C1:C6),3,FALSE)"
End Sub
Click to expand...

I don't see anywhere that the code is removing the named ranges. As such, the ranges are being deleted during your purge. Looks like what you need to do is clear the ranges and after that pages purge re-set the named ranges.
 
Upvote 0
Hi RCBricker, thanks fpr your reply. I'm afraid I'm an excel newbie... does this mean that I'd need to define my named ranges in VBA for these formulas to work after the macro has been run?
 
Upvote 0
When your macro deletes columns BEFORE the named ranges, ie, cols A:C, and B:H
The named ranges are no longer in their respective columns, as they have moved over to the left.
Try hiding the columns instead !!
 
Upvote 0
Thanks Michael for your suggestion. My macro will be run daily with different data but the columns will be the same. Does this mean I would have to manually hide the columms every day to get the macro to work correctly?
 
Upvote 0
No, you could change the delete lines like this



Code: 
Columns("A:C").Select
    Selection.Delete Shift:=xlToLeft


to
Code: 
Columns("A:C").entirecolumn.hidden=true
 
Last edited:
Upvote 0
Hi Michael, I need the deleted lines to be deleted otherwise other formulas I use do not calculate correctly as they take information from the hidden columns. Is there anyway to set named ranges in VBA so that when the macro runs it takes this into account?

Thanks!

Elsie
 
Upvote 0
Deleting the rows shouldn't be a problem.....deleting columns will cause you issues
 
Upvote 0
Hi Michael,

Unfortunately I'm not sure there is a work around for deleting the columns. Without it, the formatting of that sheet is horrendous to navigate.
Is there a way to set the Named range in excel vba after having deleted the columns so the named ranges I have created manually after deleting the columns would still function when being run in an automated fashion through vba?
 
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Question Question
VBA removing heading in spreadsheet
Replies
3
Views
273
rlv01
R
Noid67
  • Question Question
Run-time 1004 for this With statement
Replies
2
Views
176
Peter_SSs
Peter_SSs
G
  • Solved
Typing out Excel formula in Excel Macro
Replies
1
Views
86
Macropod
Macropod
M
  • Question Question
How To Loop Macro
Replies
6
Views
588
rlv01
R
R
  • Solved
Filter data set to #N/A, skip step entirely if none
Replies
3
Views
310
RoryA
RoryA

Forum statistics

Threads
1,224,823
Messages
6,181,181
Members
453,022
Latest member
Mohamed Magdi Tawfiq Emam

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