REIUSA
asked on
Need help with excel question, fill in cell below category?
Hello,
I am dealing with a spread sheet with a lot of data and each region is totaled in the sheet with a line below each section. The problem is the line with the total doesn't have any other info other than "total" and the number.
What I need to do is automate a way to fill in the far left cell of the total line with the region name so I can filter it by region and only see the totals. The sheet I am using has about 160,000 lines every month so manually doing this would be a nightmare.
Is there a easy way to accomplish this? Attached is a spread sheet that mimics what I am trying to do. I may actually need to auto fill in two of the rows down to the total line. The example is a cut down version of what I am dealing with.
EE_Excel_Q.xlsx
I am dealing with a spread sheet with a lot of data and each region is totaled in the sheet with a line below each section. The problem is the line with the total doesn't have any other info other than "total" and the number.
What I need to do is automate a way to fill in the far left cell of the total line with the region name so I can filter it by region and only see the totals. The sheet I am using has about 160,000 lines every month so manually doing this would be a nightmare.
Is there a easy way to accomplish this? Attached is a spread sheet that mimics what I am trying to do. I may actually need to auto fill in two of the rows down to the total line. The example is a cut down version of what I am dealing with.
EE_Excel_Q.xlsx
ASKER
Great thanks. If I wanted to edit it to do the same thing for column B would I change the below line? In the original form colunm B is blank on the total row.
I tried playing around with the syntax but keep getting errors.
Range("A2:A" & lr).SpecialCells(xlCellTyp eBlanks).F ormulaR1C1 = "=R[-1]C"
I tried playing around with the syntax but keep getting errors.
Range("A2:A" & lr).SpecialCells(xlCellTyp
It should be like this then...
Sub FillBlanks()
Dim lr As Long
lr = Cells(Rows.Count, 3).End(xlUp).Row
On Error Resume Next
Range("B2:B" & lr).SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=R[-1]C"
End Sub
ASKER
Thanks, when I try that it fills in for column B but not column A.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks that works great.
You're welcome REIUSA! Glad to help.
Please refer to the attached and click the button called "Fill Blanks" on Sheet1 to run the code.
Open in new window
EE_Excel_Q.xlsm