I had a worksheet table with blank rows separating the groups.
I needed to add another column – a formula – but wanted to retain the blank rows for formatting tidiness.
The table after adding the formula column:
Notice the formula produces zeros for the blank rows. I could just select each cell (D3, D5, D8, D10) and hit delete, but for thousands of rows that would be time consuming.
Here’s how I did it:
– Select column C.
– From the Edit menu, select Go To…, then click Special…
– Select Blanks, then click OK
Then I ran a macro which allows me to move the selection over one column.
In this example, I typed 0, 1 for the Input to SelectionOffset.
After the Selection was moved, I hit the delete key.
Sub SelectionOffset()
Dim strInput As String, str As String, i As Long, bln As Boolean
Dim strRows As String, strCols As String
strInput = “”
Do
bln = False
strInput = InputBox(“Selection offset by rows, cols” & vbNewLine & _
“eg. 12, 2”, “Selection offset”, strInput)
str = Replace(strInput, ” “, “”)
If str <> “” Then
i = InStr(str, “,”)
If i = 0 Then strRows = str Else strRows = IIf(i = 1, “0”, Left(str, i – 1))
If i = 0 Or i = Len(str) Then strCols = “0” Else strCols = Mid(str, i + 1)
If IsNumeric(strRows) And IsNumeric(strCols) Then
On Error Resume Next
Selection.Offset(strRows, strCols).Select
If Err.Number <> 0 Then
MsgBox “Invalid selection offset”, vbExclamation, “Error”
bln = True
End If
On Error GoTo 0
Else
MsgBox “Selection offset is not numeric”, vbExclamation, “Error”
bln = True
End If
End If
Loop While bln
End Sub
Dim strInput As String, str As String, i As Long, bln As Boolean
Dim strRows As String, strCols As String
strInput = “”
Do
bln = False
strInput = InputBox(“Selection offset by rows, cols” & vbNewLine & _
“eg. 12, 2”, “Selection offset”, strInput)
str = Replace(strInput, ” “, “”)
If str <> “” Then
i = InStr(str, “,”)
If i = 0 Then strRows = str Else strRows = IIf(i = 1, “0”, Left(str, i – 1))
If i = 0 Or i = Len(str) Then strCols = “0” Else strCols = Mid(str, i + 1)
If IsNumeric(strRows) And IsNumeric(strCols) Then
On Error Resume Next
Selection.Offset(strRows, strCols).Select
If Err.Number <> 0 Then
MsgBox “Invalid selection offset”, vbExclamation, “Error”
bln = True
End If
On Error GoTo 0
Else
MsgBox “Selection offset is not numeric”, vbExclamation, “Error”
bln = True
End If
End If
Loop While bln
End Sub
Could you not have used an autofilter, selected “0?, F5>Special…>visible cells, Delete?
-Enjoy your site!
Why not use the formula : = IF (C2> 2; IF (B2 = “T” ,2,1) * C2; “”)
Sorry… : IF (C2> 0; IF (B2 = “T” ,2,1) * C2; “”)
If “” Cells in Column “D” must be blank use this simple macro:
Range(“D:D”).SpecialCells(xlCellTypeFormulas, xlTextValues).ClearContents
Hi,
just to extent the proposal of “the Okk”
Use the formula =IF(C2>0,IF(B2=”T”,2,1)*C2,1/C2) and copy down.
In cells D3, D5 etc. you got errors #DIV/0!
Select the column D
Hit F5, Go To,
Click Formulas and unclick everything but errors…
Press OK and then Delete.
My best regards
Jiri Cihar
http://www.dataspectrum.cz
Another approach: Hard code a sort key in Col.E with Excel’s Edit->Fill->Series… Sort on Col. A to group the blank rows. Delete romulas in D and resort on the key.
Brett
I try and avoid blank rows, I prefer to use the row height to sort the formatting out.
I like the select special errors approach, Bob has that in his Excel magic book too.
cheers
Simon
As an alternative
=IF ( B2=”” , “” , IF (B2 = “T” ,2,1) * C2 )
or
You could just leave the original formula as is and
use a Conditional Format Font colour of White for
Blank or Zero value cells in Column D
or if you wanted to use a macro to insert just the values
in the non blank rows
For I = 2 to Columns(1).Find(“*”,,,,,xlPrevious).Row
If Cells(I,2) = “S” then
Cells(I,4) = Cells(I,3)
ElseIf Cells(I,2) = “T” then
Cells(I,4) = Cells(I,3)*2
EndIf
Next I
or you could just use the same to insert the Formula if
this is actually needed
Kind regards
Couldn’t you have filtered column D for all cells equal to zero? Then use Edit|Go To|Special|Visible Cells Only and press Delete.
Neil,
As long as nobody’s Weighted (col D) evaluated to 0. But you could filter A, B, or C for blanks!
Brett
You should also be able to just filter a column for non-blanks and use ctrl+enter to enter the formulas. Note that going to visible cells shouldn’t be needed as operations in filtered view only apply to the visible selection.
Hello,
try this to fill column D in the required rows with the formula in question as per your example:
Sub Fill_not_empties()
Dim lz As Long
lz = Cells(Rows.Count, 3).End(xlUp).Row
Range(“C2:C” & lz).SpecialCells(xlCellTypeConstants, 23) _
.Offset(0, 1).FormulaR1C1 = “=IF(RC[-2]=””T””,2,1)*RC[-1]” ‘Column D
End Sub
Regards
Beate
I always use the formula:
=IF ( B2=”” , “” , IF (B2 = “T” ,2,1) * C2 )
Works every time & I do it without thinking now.
When I first saw the problem I thought keep it simple. Came up with the formula a Andrew Hunt. Then I scrolled down. I am a Geologist an work with Geotech and Engineers. I like to avoid macros, I find it hard for fellow workers to follow or edit. Often other will asked for my help. I like to keep it simple for them and to predict incorrect inputs. This is the choice I would select, If someone put in the wrong data it will still produce a blank space
=IF(B1=”T”,2*C1,IF(B1=”S”,C1*1,””))