Rubin Shrestha
Zend certified PHP/Magento developer
Rubin Shrestha
Blog

Trying to update ranges in macro to not have set range

by |Published

I am trying to update my macro to where the set ranges in the macro are not hard coded because when the data for the new data set is ran the data range may be smaller or larger. How do I update my macro to show this.

I thought using .range(ss) or using a worksheet formula would work but keep getting synax error

Sub Macro1()
'
' Macro1 Macro
'
'
Sheets(Array("NPI < 10 digits", "Duplicate NPI", "Blank NPI", "Old Raw Data", _
"old not in new", "new not in old", "Blank DOB ")).Select
Sheets("Blank DOB ").Activate
Cells.Select
Selection.Delete Shift:=xlUp
Sheets("New Raw Data").Select
Cells.Select
Range("U1").Activate
Selection.Copy
Sheets("Old Raw Data").Select
Range("A1").Select
ActiveSheet.Paste
Sheets("New Raw Data").Select
Cells.Select
Application.CutCopyMode = False
Selection.Delete Shift:=xlUp
Range("A1").Select
Dim ws As Worksheet, strFile As String
Set ws = ActiveWorkbook.Sheets("New Raw Data") 'set to current worksheet name
strFile = Application.GetOpenFilename
With ws.QueryTables.Add(Connection:="TEXT;" & strFile, Destination:=ws.Range("A1"))
.TextFileParseType = xlDelimited
.TextFileOtherDelimiter = "|"
.Refresh
End With
Selection.AutoFilter
End Sub
Sub Macro7()
'
' Macro7 Macro
'
Sheets("New Raw Data").Range("AB:AB").Copy Destination:=Sheets("NPI < 10 digits").Range("A1")
Sheets("NPI < 10 digits").Select
Range("A1").Select
Selection.AutoFilter
Sheets("New Raw Data").Range("AB:AB").Copy Destination:=Sheets("Duplicate NPI").Range("A1")
Sheets("Duplicate NPI").Select
Range("A1").Select
Selection.AutoFilter
Sheets("New Raw Data").Range("AB:AB").Copy Destination:=Sheets("Blank DOB").Range("A1")
Sheets("Duplicate NPI").Select
Range("A1").Select
Selection.AutoFilter
Sheets("New Raw Data").Range("AB:AB").Copy Destination:=Sheets("Blank NPI").Range("A1")
Sheets("Blank NPI").Select
Range("A1").Select
Selection.AutoFilter
Sheets("New Raw Data").Range("A:AK").Copy Destination:=Sheets("new not in old").Range("A1")
Sheets("new not in old").Select
Range("A1").Select
Selection.AutoFilter
Sheets("Old Raw Data").Range("A:AK").Copy Destination:=Sheets("old not in new").Range("A1")
Sheets("old not in new").Select
Range("A1").Select
Selection.AutoFilter
Sheets("old not in new").Select
Range("A1").Select
End Sub
Sub Macro8()
'Macro8 Macro
Sheets(Array("NPI < 10 digits", "Duplicate NPI", "Blank NPI", "Old Raw Data", _
"old not in new", "new not in old")).Select
Sheets("Blank NPI").Activate
Cells.Select
Selection.Delete Shift:=xlUp
Sheets("New Raw Data").Select
Cells.Select
Range("U1").Activate
Selection.Copy
Sheets("Old Raw Data").Select
Range("A1").Select
ActiveSheet.Paste
Sheets("New Raw Data").Select
Cells.Select
Application.CutCopyMode = False
Selection.Delete Shift:=xlUp
Range("A1").Select
Dim ws As Worksheet, strFile As String
Set ws = ActiveWorkbook.Sheets("New Raw Data") 'set to current worksheet name
strFile = Application.GetOpenFilename
With ws.QueryTables.Add(Connection:="TEXT;" & strFile, Destination:=ws.Range("A1"))
.TextFileParseType = xlDelimited
.TextFileOtherDelimiter = "|"
.Refresh
End With
Selection.AutoFilter
Sheets("New Raw Data").Range("AB:AB").Copy Destination:=Sheets("NPI < 10 digits").Range("A1")
Sheets("NPI < 10 digits").Select
Range("A1").Select
Selection.AutoFilter
Sheets("New Raw Data").Range("AB:AB").Copy Destination:=Sheets("Duplicate NPI").Range("A1")
Sheets("Duplicate NPI").Select
Range("A1").Select
Selection.AutoFilter
Sheets("New Raw Data").Range("AB:AB").Copy Destination:=Sheets("Blank NPI").Range("A1")
Sheets("Blank NPI").Select
Range("A1").Select
Sheets("New Raw Data").Range("A:AK").Copy Destination:=Sheets("new not in old").Range("A1")
Sheets("new not in old").Select
Range("A1").Select
Selection.AutoFilter
Sheets("Old Raw Data").Range("A:AK").Copy Destination:=Sheets("old not in new").Range("A1")
Sheets("old not in new").Select
Range("A1").Select
Selection.AutoFilter
Sheets("old not in new").Select
Range("A1").Select
End Sub
Sub Macro12()
'
' Macro12 Macro
'
'
Sheets("new not in old").Select
Columns("AB:AB").Select
Selection.Cut
Columns("A:A").Select
Selection.Insert Shift:=xlToRight
Range("A1").Select
ActiveWorkbook.Worksheets("new not in old").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("new not in old").Sort.SortFields.Add Key:=Range( _
"A1"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets("new not in old").Sort
Worksheets("new not in old").Range("A2:AK2", "A:AK" & Worksheets("new not in old").Range("A:AK" & Rows.Count).End(xlUp).Row).Select
.Range (ss)
.Header = xlNo
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Selection.AutoFilter
Selection.AutoFilter
ActiveWorkbook.Worksheets("new not in old").AutoFilter.Sort.SortFields.Clear
ActiveWorkbook.Worksheets("new not in old").AutoFilter.Sort.SortFields.Add Key _
Worksheets("new not in old").Range("A1", "A" & Worksheets("new not in old").Range("A" & Rows.Count).End(xlUp).Row).Select, SortOn:=xlSortOnValues,Order:=xlAscending, _
DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("new not in old").AutoFilter.Sort
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Sheets("old not in new").Select
Selection.AutoFilter
Columns("AB:AB").Select
Selection.Cut
Columns("A:A").Select
Selection.Insert Shift:=xlToRight
Range("A1").Select
Selection.AutoFilter
ActiveWorkbook.Worksheets("old not in new").AutoFilter.Sort.SortFields.Clear
ActiveWorkbook.Worksheets("old not in new").AutoFilter.Sort.SortFields.Add Key _
Worksheets ("old not in new") .Range  ("A1", "A" & worksheets ("old not in new") .range('A" & rows .Count) .end(xlup) .row ) .Select, SortOn:=xlSortOnValues, Order:=xlAscending, _
DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("old not in new").AutoFilter.Sort
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Columns("B:B").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Range("B1").Select
Sheets("new not in old").Select
Columns("B:B").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Range("B1").Select
End Sub
Sub Macro13()
'
' Macro13 Macro
'
'
Sheets("old not in new").Select
Range("B2").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-1],'new not in old'!C[-1],1,FALSE)"
Range("B2").Select
Selection.AutoFill Destination:=Range(ss)
Range(ss).Select
Sheets("new not in old").Select
Range("B2").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-1],'old not in new'!C[-1],1,FALSE)"
Range("B2").Select
Selection.AutoFill Destination:=Range(ss)
Range(ss).Select
Range("B1").Select
End Sub
Sub Macro14()
' Macro14 Macro
'
Sheets("new not in old").Select
Columns("AB:AB").Select
Selection.Cut
Columns("A:A").Select
Selection.Insert Shift:=xlToRight
Range("A1").Select
ActiveWorkbook.Worksheets("new not in old").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("new not in old").Sort.SortFields.Add Key:=Range( _
"A1"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets("new not in old").Sort
Worksheets ("new not in old").Range("A2:AK2", "A:AK" & worksheets ("new not in old").range("A" & rows.Count).end(xlup).row).Select,
.Range (ss)
.Header = xlNo
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Selection.AutoFilter
Selection.AutoFilter
ActiveWorkbook.Worksheets("new not in old").AutoFilter.Sort.SortFields.Clear
ActiveWorkbook.Worksheets("new not in old").AutoFilter.Sort.SortFields.Add Key _
Worksheets ("new not in old").Range("A1", "A" & worksheets ("new not in old").range("A" & rows.Count).end(xlup).row).Select, SortOn:=xlSortOnValues, Order:=xlAscending, _
DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("new not in old").AutoFilter.Sort
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Sheets("old not in new").Select
Selection.AutoFilter
Columns("AB:AB").Select
Selection.Cut
Columns("A:A").Select
Selection.Insert Shift:=xlToRight
Range("A1").Select
Selection.AutoFilter
ActiveWorkbook.Worksheets("old not in new").AutoFilter.Sort.SortFields.Clear
ActiveWorkbook.Worksheets("old not in new").AutoFilter.Sort.SortFields.Add Key _
Worksheets ("old not in new").Range("A1", "A" & worksheets ("old not in new").range("A" & rows.Count).end(xlup).row).Select, SortOn:=xlSortOnValues, Order:=xlAscending, _
DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("old not in new").AutoFilter.Sort
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Columns("B:B").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Range("B1").Select
Sheets("new not in old").Select
Columns("B:B").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Range("B1").Select
Sheets("old not in new").Select
Range("B2").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-1],'new not in old'!C[-1],1,FALSE)"
Range("B2").Select
Selection.AutoFill Destination:=Range(ss)
Range(ss).Select
Sheets("new not in old").Select
Range("B2").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-1],'old not in new'!C[-1],1,FALSE)"
Range("B2").Select
Selection.AutoFill Destination:=Range(ss)
Range(ss).Select
Range("B1").Select
End Sub
Sub Macro15()
Sheets(Array("NPI < 10 digits", "Duplicate NPI", "Blank NPI", "Old Raw Data", _
"old not in new", "new not in old")).Select
Sheets("Blank NPI").Activate
Cells.Select
Selection.Delete Shift:=xlUp
Sheets("New Raw Data").Select
Cells.Select
Range("U1").Activate
Selection.Copy
Sheets("Old Raw Data").Select
Range("A1").Select
ActiveSheet.Paste
Sheets("New Raw Data").Select
Cells.Select
Application.CutCopyMode = False
Selection.Delete Shift:=xlUp
Range("A1").Select
Dim ws As Worksheet, strFile As String
Set ws = ActiveWorkbook.Sheets("New Raw Data") 'set to current worksheet name
strFile = Application.GetOpenFilename
With ws.QueryTables.Add(Connection:="TEXT;" & strFile, Destination:=ws.Range("A1"))
.TextFileParseType = xlDelimited
.TextFileOtherDelimiter = "|"
.Refresh
End With
Selection.AutoFilter
Range("A1").Select
Columns("AB:AB").Select
ActiveWorkbook.Worksheets("New Raw Data").AutoFilter.Sort.SortFields.Clear
ActiveWorkbook.Worksheets("New Raw Data").AutoFilter.Sort.SortFields.Add _
Key:=Range("AB:AB"), SortOn:=xlSortOnValues, Order:=xlAscending, _
DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("New Raw Data").AutoFilter.Sort
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Sheets("New Raw Data").Range("AB:AB").Copy Destination:=Sheets("NPI < 10 digits").Range("A1")
Sheets("NPI < 10 digits").Select
Range("A:A").Select
Selection.AutoFilter
Range("A1").Select
Sheets("New Raw Data").Range("AB:AB").Copy Destination:=Sheets("Duplicate NPI").Range("A1")
Sheets("Duplicate NPI").Select
Range("A1").Select
Selection.AutoFilter
Sheets("New Raw Data").Range("A:AK").Copy Destination:=Sheets("Blank NPI").Range("A1")
Sheets("Blank NPI").Select
Range("A1").Select
Selection.AutoFilter
Sheets("New Raw Data").Range("A:AK").Copy Destination:=Sheets("new not in old").Range("A1")
Sheets("new not in old").Select
Range("A1").Select
Selection.AutoFilter
Sheets("Old Raw Data").Range("A:AK").Copy Destination:=Sheets("old not in new").Range("A1")
Sheets("old not in new").Select
Range("A1").Select
Selection.AutoFilter
Sheets("old not in new").Select
Range("A1").Select
Sheets("new not in old").Select
Columns("AB:AB").Select
Selection.Cut
Columns("A:A").Select
Selection.Insert Shift:=xlToRight
Range("A1").Select
ActiveWorkbook.Worksheets("new not in old").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("new not in old").Sort.SortFields.Add Key:=Range( _
"A1"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets("new not in old").Sort
Worksheets("new not in old").Range("A2:AK2", "A:AK" & Worksheets("new not in old").Range("A:AK" & Rows.Count).End(xlUp).Row).Select
.Header = xlNo
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Selection.AutoFilter
Selection.AutoFilter
ActiveWorkbook.Worksheets("new not in old").AutoFilter.Sort.SortFields.Clear
ActiveWorkbook.Worksheets("new not in old").AutoFilter.Sort.SortFields.Add Key _
Worksheets("new not in old").Range("A1", "A" & Worksheets("new not in old").Range("A" & Rows.Count).End(xlUp).Row).Select, SortOn:=xlSortOnValues, Order:=xlAscending,
DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("new not in old").AutoFilter.Sort
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Sheets("old not in new").Select
Selection.AutoFilter
Columns("AB:AB").Select
Selection.Cut
Columns("A:A").Select
Selection.Insert Shift:=xlToRight
Range("A1").Select
Selection.AutoFilter
ActiveWorkbook.Worksheets("old not in new").AutoFilter.Sort.SortFields.Clear
ActiveWorkbook.Worksheets("old not in new").AutoFilter.Sort.SortFields.Add Key _
:=Range("A1:A5000"), SortOn:=xlSortOnValues, Order:=xlAscending, _
DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("old not in new").AutoFilter.Sort
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Columns("B:B").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Range("B1").Select
Sheets("new not in old").Select
Columns("B:B").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Range("B1").Select
Sheets("old not in new").Select
Range("B2").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-1],'new not in old'!C[-1],1,FALSE)"
Range("B2").Select
Selection.AutoFill Destination:=Range(ss)
Range(ss).Select
Sheets("new not in old").Select
Range("B2").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-1],'old not in new'!C[-1],1,FALSE)"
Range("B2").Select
Selection.AutoFill Destination:=Range(ss)
Range(ss).Select
Range("B1").Select
End Sub

You may also like