Selection Offset
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
BrianV:
Could you not have used an autofilter, selected "0", F5>Special...>visible cells, Delete?
-Enjoy your site!
25 January 2007, 6:46 pmthe Okk:
Why not use the formula : = IF (C2> 2; IF (B2 = "T" ,2,1) * C2; "")
25 January 2007, 11:15 pmthe Okk:
Sorry... : IF (C2> 0; IF (B2 = "T" ,2,1) * C2; "")
25 January 2007, 11:17 pmthe Okk:
If "" Cells in Column "D" must be blank use this simple macro:
25 January 2007, 11:36 pmRange("D:D").SpecialCells(xlCellTypeFormulas, xlTextValues).ClearContents
Jiri Cihar:
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
26 January 2007, 5:04 amhttp://www.dataspectrum.cz
Brett:
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
26 January 2007, 6:21 amsimon murphy:
I try and avoid blank rows, I prefer to use the row height to sort the formatting out.
26 January 2007, 6:24 amI like the select special errors approach, Bob has that in his Excel magic book too.
cheers
Simon
Andrew Hunt:
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
26 January 2007, 6:42 amNeil:
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.
26 January 2007, 7:20 amBrett:
Neil,
As long as nobody's Weighted (col D) evaluated to 0. But you could filter A, B, or C for blanks!
Brett
26 January 2007, 8:24 amLori:
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.
27 January 2007, 8:13 amBeate Schmitz:
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
30 January 2007, 2:18 pmBeate
Jules:
I always use the formula:
=IF ( B2="" , "" , IF (B2 = "T" ,2,1) * C2 )
Works every time & I do it without thinking now.
1 February 2007, 3:20 amLarry Douglas:
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
1 February 2007, 6:55 am=IF(B1="T",2*C1,IF(B1="S",C1*1,""))