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.

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,””))