Link to home
Start Free TrialLog in
Avatar of REIUSA
REIUSAFlag for United States of America

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
Avatar of Subodh Tiwari (Neeraj)
Subodh Tiwari (Neeraj)
Flag of India image

You can do that with the help of a small piece of VBA code.
Please refer to the attached and click the button called "Fill Blanks" on Sheet1 to run the code.
Sub FillBlanks()
Dim lr As Long
lr = Cells(Rows.Count, 2).End(xlUp).Row
On Error Resume Next
Range("A2:A" & lr).SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=R[-1]C"
End Sub

Open in new window

EE_Excel_Q.xlsm
Avatar of REIUSA

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(xlCellTypeBlanks).FormulaR1C1 = "=R[-1]C"
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

Open in new window

Avatar of REIUSA

ASKER

Thanks, when I try that it fills in for column B but not column A.
ASKER CERTIFIED SOLUTION
Avatar of Subodh Tiwari (Neeraj)
Subodh Tiwari (Neeraj)
Flag of India image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of REIUSA

ASKER

Thanks that works great.
You're welcome REIUSA! Glad to help.