Example when I export an invoice, first line is complete but succeeding lines for the 2nd and so on invoice items have blank cells that indicate they are part of the first row. What I need to fill those blank cells with the values in the first row repeating so that there are no blank rows. It facilitates pivot processing using other spreadsheet tools.
Odoo is the world's easiest all-in-one management software.
It includes hundreds of business apps:
- CRM
- e-Commerce
- Accounting
- Inventory
- PoS
- Project management
- MRP
This question has been flagged
1
Reply
1781
Views
I created an excel macro to fill blanks with the last value on top applying it to the selected a range. Sharing my code to fill down and fill up.
STEPS:
(1) select the range you want to process then
(2) run the macro
Sub FillInBlanksWithLastValue()
oldStatusBar = Application.DisplayStatusBar
Application.DisplayStatusBar = True
prevVal = ""
For Each c In Selection
Application.StatusBar = "PROCESSING ROW: " & c.Row & " / " & Selection\\.Rows\\.Count
\\ If\\ Len\\(c\\.Value\\)\\ =\\ 0\\ Then
\\ \\ c\\.Value\\ =\\ prevVal
\\ Else
\\ \\ prevVal\\ =\\ c\\.Value
\\ End\\ If
Next
\\ \\ \\ \\ Application\\.StatusBar\\ =\\ False
Application\\.DisplayStatusBar\\ =\\ oldStatusBar
End\\ Sub
'\\-\\-\\-\\-\\-\\-\\-\\-\\-\\-\\-\\-\\-\\-\\-\\-\\-\\-\\-\\-\\-\\-
Sub\\ ReverseFillInBlanksWithLastValue\\(\\)
Dim\\ cell\\ As\\ Range
Dim\\ Width\\ As\\ Long
Dim\\ Height\\ As\\ Long
\\ \\ \\ \\ oldStatusBar\\ =\\ Application\\.DisplayStatusBar
Application\\.DisplayStatusBar\\ =\\ True
prevVal\\ =\\ ""
For\\ i\\ =\\ Selection\\.Rows\\.Count\\ To\\ 1\\ Step\\ \\-1
\\ Application\\.StatusBar\\ =\\ "PROCESSING\\ ROW:\\ "\\ \\&\\ Selection\\.item\\(i\\)\\.Row\\ \\&\\ "\\ /\\ "\\ \\& Selection.Rows.Count
If Len(Selection.item(i).Value) = 0 Then
Selection.item(i).Value = prevVal
Else
prevVal = Selection.item(i).Value
End If
Next
Application.StatusBar = False
Application.DisplayStatusBar = oldStatusBar
End Sub
I hope this helps (Just remove the back slashes in the code .. this forum automatically puts them
Enjoying the discussion? Don't just read, join in!
Create an account today to enjoy exclusive features and engage with our awesome community!
Sign up
one-value1, one-value2, many-value1-1, many-value1-2
<blank>, <blank> , many-value2-1, many-value2-2
<blank>, <blank> , many-value3-1, many-value3-2
what I want is if possible to have
one-value1, one-value2, many-value1-1, many-value1-2
one-value1, one-value2, many-value2-1, many-value2-2
one-value1, one-value2, many-value3-1, many-value3-2
Did you find any solution here ?