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 = “”
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
If Err.Number <> 0 Then
MsgBox “Invalid selection offset”, vbExclamation, “Error”
bln = True
On Error GoTo 0
MsgBox “Selection offset is not numeric”, vbExclamation, “Error”
bln = True
Loop While bln