Recorded Macro but isn't working properly?

YIDA

New Member
Joined
Jul 6, 2021
Messages
5
Office Version
  1. 365
Platform
  1. Windows
Hello Excel Pros,


I am working in Production and everyday I will received a Work In Progress excel sheet
Everyday I had to go over the same action just to show me the relevant data I needed
Until recently I found out about recording Macro and decided to try it
I started recording, click stop recording, tried to run it. But everything went wrong.
How can I fix this?
The purpose of my recording is to
1. Hide a few cells that are not relevant to me
2. Sort date oldest to newest
3. Filter out WIP for shift A (there are 2 shifts)
4. Sum up 2 rows and filter out the numbers (less than or equal to) 50
I tried to find the problem of where went wrong by breaking down into 4 recordings, and I found out that the Step 1: the hide macros are already messed up.
Below is the code.
' wip Macro
'
'
Range("K1247").Select
ActiveCell.FormulaR1C1 = "a"
Columns("A:C").Select
Selection.EntireColumn.Hidden = True
Rows("7:7").Select
Selection.UnMerge
ActiveWorkbook.Worksheets("07-03").AutoFilter.Sort.SortFields.Clear
ActiveWorkbook.Worksheets("07-03").AutoFilter.Sort.SortFields.Add Key:=Range( _
"D7:D1248"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets("07-03").AutoFilter.Sort
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
ActiveSheet.Range("$A$7:$CV$1248").AutoFilter Field:=11, Criteria1:="A"
Columns("AC:AU").Select
Selection.EntireColumn.Hidden = True
Columns("AZ:BK").Select
Selection.EntireColumn.Hidden = True
Columns("BP:CE").Select
Selection.EntireColumn.Hidden = True
Range("CU10").Select
ActiveCell.FormulaR1C1 = "=RC[-33]+RC[-49]"
Range("CU10").Select
Selection.FillDown
ActiveSheet.Range("$A$7:$CV$1248").AutoFilter Field:=99, Criteria1:="<=-50" _
, Operator:=xlAnd
ActiveWindow.ScrollColumn = 28
ActiveWindow.ScrollColumn = 24
ActiveWindow.ScrollColumn = 13
ActiveWindow.ScrollColumn = 12
ActiveWindow.ScrollColumn = 10
ActiveWindow.SmallScroll Down:=51
End Sub


Please help!
Many thanks!
 

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.

YIDA

New Member
Joined
Jul 6, 2021
Messages
5
Office Version
  1. 365
Platform
  1. Windows
So when you say "the rest are hidden". Can you confirm that its not the grouping that is hiding the rest ?
No it is not. When I clicked on it, it expands on the column which are hidden by the author, it does not affect my recorded macro.

However, I am starting to think that due to certain formatting or formula that has done by the author, my recorded macro cannot properly adjust to what I want it to do. Therefore, the error.
Is there anyway to reset the formatting or formula before I start my recording again?
 

Alex Blakenburg

Well-known Member
Joined
Feb 23, 2021
Messages
2,247
Office Version
  1. 365
Platform
  1. Windows
No it is not. When I clicked on it, it expands on the column which are hidden by the author, it does not affect my recorded macro.

However, I am starting to think that due to certain formatting or formula that has done by the author, my recorded macro cannot properly adjust to what I want it to do. Therefore, the error.
Is there anyway to reset the formatting or formula before I start my recording again?

The most likely cause of your issue is that you have merged cells. When you are selecting a column it is picking up "all" the columns involved in the merge.

Try hiding the columns without using the select statement.
(If there are other similar statements elsewhere in the code make sure you make the change there too)

So replace these:-
VBA Code:
    Columns("A:C").Select
    Selection.EntireColumn.Hidden = True

    Columns("AC:AU").Select
    Selection.EntireColumn.Hidden = True
    Columns("AZ:BK").Select
    Selection.EntireColumn.Hidden = True
    Columns("BP:CE").Select
    Selection.EntireColumn.Hidden = True

With this
VBA Code:
    Columns("A:C").Hidden = True

    Columns("AC:AU").Hidden = True
    Columns("AZ:BK").Hidden = True
    Columns("BP:CE").Hidden = True
 

Forum statistics

Threads
1,147,571
Messages
5,741,895
Members
423,693
Latest member
Excelquestion35

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