Clean up blanks in columns

artz

Well-known Member
Joined
Aug 11, 2002
Messages
791
Hi,

I have an input area on a worksheet that is two columns wide, columns O and P. Data are entered or deleted in pairs. I want to clean up gaps when pairs are deleted without upsetting the formatting.

Deleting whole rows is not useable because it effects other parts of the worksheet and the formatting of the input area. I've tried more than a dozen macros that I found online and so far, every one messes up the worksheet.

It seems like the macro should copy, then paste the values one row up into the now blank row. I don't know how it would do this for multiple blank rows in the columns.

Does anyone in the Forum have an idea for a macro that could do this?

Thanks,

Art
 

Some videos you may like

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.

GCExcel

New Member
Joined
Apr 11, 2013
Messages
48
Hello,

Here's a suggestion. Copy the range values in an array, then loop through the array and remove the blank rows, then copy back.

Code:
Sub RemoveBlanks()    Dim rg As Range
    Dim ar, i As Long, n As Long
    
    Set rg = Range("B7:C50")   'CHANGE RANGE AS REQUIRED
    
    ar = rg.Value
    n = 1
    For i = 1 To UBound(ar, 1)
        If ar(i, 1) <> "" Or ar(i, 2) <> "" Then
            ar(n, 1) = ar(i, 1): ar(n, 2) = ar(i, 2)
            n = n + 1
        End If
    Next i
    
    rg.ClearContents
    rg.Cells(1).Resize(n - 1, 2) = ar


End Sub
 

artz

Well-known Member
Joined
Aug 11, 2002
Messages
791
Hi GCExcel,

Thanks for your response and code suggestion. When I posted the request, I hadn't noticed because it was off-screen, that I also had a third helper column as shown below:
<style type="text/css">
table.tableizer-table {
border: 1px solid #CCC; font-family: Arial, Helvetica, sans-serif;
font-size: 12px;
}
.tableizer-table td {
padding: 4px;
margin: 3px;
border: 1px solid #ccc;
}
.tableizer-table th {
background-color: #104E8B;
color: #FFF;
font-weight: bold;
}
</style><table class="tableizer-table">
<tr class="tableizer-firstrow"><th>Long Ticker Symbol</th><th>ShortTicker Symbol</th><th>Combination</th></tr>
<tr><td>DIG</td><td>DUG</td><td>DIG DUG</td></tr>
<tr><td> </td><td> </td><td> </td></tr>
<tr><td>EDC</td><td>EDZ</td><td>EDC EDZ</td></tr>
<tr><td>NUGT</td><td>DUST</td><td>NUGT DUST</td></tr>
<tr><td>QLD</td><td>QID</td><td>QLD QID</td></tr>
<tr><td> </td><td> </td><td> </td></tr>
<tr><td>RXL</td><td>RXD</td><td>RXL RXD</td></tr>
<tr><td>UYM</td><td>SMN</td><td>UYM SMN</td></tr>
</table>

What is not obvious is the formulas shown below:
<style type="text/css">
table.tableizer-table {
border: 1px solid #CCC; font-family: Arial, Helvetica, sans-serif;
font-size: 12px;
}
.tableizer-table td {
padding: 4px;
margin: 3px;
border: 1px solid #ccc;
}
.tableizer-table th {
background-color: #104E8B;
color: #FFF;
font-weight: bold;
}
</style><table class="tableizer-table">
<tr class="tableizer-firstrow"><th>Long Ticker Symbol</th><th>ShortTicker Symbol</th><th>Combination</th></tr>
<tr><td>DIG</td><td>DUG</td><td>=O2&" " &P2</td></tr>
<tr><td> </td><td> </td><td> </td></tr>
<tr><td>EDC</td><td>EDZ</td><td>=O4&" " &P4</td></tr>
<tr><td>NUGT</td><td>DUST</td><td>=O5&" " &P5</td></tr>
<tr><td> </td><td> </td><td> </td></tr>
<tr><td>ROM</td><td>REW</td><td>=O7&" " &P7</td></tr>
<tr><td>RXL</td><td>RXD</td><td>=O8&" " &P8</td></tr>
<tr><td>UYM</td><td>SMN</td><td>=O9&" " &P9</td></tr>
</table>

When I run your code, the Combination column formulas get deleted. Is there a way to Preserve those formulas? They are needed for another operation.

Thanks,

Art
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,675
Office Version
  1. 2010
Platform
  1. Windows
When I run your code, the Combination column formulas get deleted. Is there a way to Preserve those formulas? They are needed for another operation.
Here is a non-looping macro which I believe does what you want. Note that since you did not tell us the column where your formulas are, I assumed they are in Column Q... I highlighted all the Q's that you would need to change if that guess was wrong.
Code:
Sub RemoveDoubleBlanksColumnsOandP()
  Dim LastRow As Long, Frmla As Variant
  LastRow = Cells.Find("*", , xlValues, , xlRows, xlPrevious).Row
  Frmla = Range("[B][COLOR="#FF0000"]Q[/COLOR][/B]1:[B][COLOR="#FF0000"]Q[/COLOR][/B]" & LastRow).Formula
  Range("O1:O" & LastRow) = Evaluate(Replace("IF(LEN(O1:O@&P1:P@),O1:O@,""#N/A"")", "@", LastRow))
  On Error Resume Next
  Intersect(Columns("O").SpecialCells(xlConstants, xlErrors).EntireRow, Columns("O:P")).Delete xlShiftUp
  On Error GoTo 0
  Range("[B][COLOR="#FF0000"]Q[/COLOR][/B]1:[B][COLOR="#FF0000"]Q[/COLOR][/B]" & LastRow).Formula = Frmla
End Sub
 

artz

Well-known Member
Joined
Aug 11, 2002
Messages
791

ADVERTISEMENT

Hi Rick,

Thanks for your response and code. Your guess was correct, the helper column is Q Data starts at row 2 in columns O,P, and Q. Below is a before running your code:
<style type="text/css">
table.tableizer-table {
border: 1px solid #CCC; font-family: Arial, Helvetica, sans-serif;
font-size: 12px;
}
.tableizer-table td {
padding: 4px;
margin: 3px;
border: 1px solid #ccc;
}
.tableizer-table th {
background-color: #104E8B;
color: #FFF;
font-weight: bold;
}
</style><table class="tableizer-table">
<tr class="tableizer-firstrow"><th>Long Ticker Symbol</th><th>ShortTicker Symbol</th><th>Combination</th></tr>
<tr><td>DIG</td><td>DUG</td><td>DIG DUG</td></tr>
<tr><td>DZK</td><td>DPK</td><td>DZK DPK</td></tr>
<tr><td> </td><td> </td><td> </td></tr>
<tr><td>NUGT</td><td>DUST</td><td>NUGT DUST</td></tr>
<tr><td>QLD</td><td>QID</td><td>QLD QID</td></tr>
<tr><td> </td><td> </td><td> </td></tr>
<tr><td>RXL</td><td>RXD</td><td>RXL RXD</td></tr>
<tr><td>UYM</td><td>SMN</td><td>UYM SMN</td></tr>
</table>
After running your code, it's partly right but missed a couple of entries in the helper column:

<style type="text/css">
table.tableizer-table {
border: 1px solid #CCC; font-family: Arial, Helvetica, sans-serif;
font-size: 12px;
}
.tableizer-table td {
padding: 4px;
margin: 3px;
border: 1px solid #ccc;
}
.tableizer-table th {
background-color: #104E8B;
color: #FFF;
font-weight: bold;
}
</style><table class="tableizer-table">
<tr class="tableizer-firstrow"><th>LONG TICKER SYMBOL</th><th>ShortTicker Symbol</th><th>Combination</th></tr>
<tr><td>DIG</td><td>DUG</td><td>DIG DUG</td></tr>
<tr><td>DZK</td><td>DPK</td><td>DZK DPK</td></tr>
<tr><td>NUGT</td><td>DUST</td><td> </td></tr>
<tr><td>QLD</td><td>QID</td><td>QLD QID</td></tr>
<tr><td>RXL</td><td>RXD</td><td>RXL RXD</td></tr>
<tr><td>UYM</td><td>SMN</td><td></td></tr>
</table>

Do you have any ideas to fix this?

Thanks,

Art
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,675
Office Version
  1. 2010
Platform
  1. Windows
Do you have any ideas to fix this?
When your users delete the data from Columns O and P, do they also delete the formula in Column Q as well (that's what you are showing me appears to be to me)?
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,675
Office Version
  1. 2010
Platform
  1. Windows

ADVERTISEMENT

When your users delete the data from Columns O and P, do they also delete the formula in Column Q as well (that's what you are showing me appears to be to me)?
If so, this modification to my code should work for you...
Code:
Sub RemoveDoubleBlanksColumnsOandP()
  Dim LastRow As Long
  LastRow = Cells.Find("*", , xlValues, , xlRows, xlPrevious).Row
  Range("O2:O" & LastRow) = Evaluate(Replace("IF(LEN(O2:O@&P2:P@),O2:O@,""#N/A"")", "@", LastRow))
  On Error Resume Next
  Intersect(Columns("O").SpecialCells(xlConstants, xlErrors).EntireRow, Columns("O:P")).Delete xlShiftUp
  On Error GoTo 0
  Range("Q2:Q" & LastRow).Formula = "=O2&"" ""&P2"
End Sub
 

artz

Well-known Member
Joined
Aug 11, 2002
Messages
791
Hi Rick,

Thanks again. I didn't realize this myself- fooled me. First answer is no, the user does not delete anything in column Q. The way the screen is setup, they don't even see it. What I missed, is that the formula is still in the Q cell, but it isn't visible because the adjacent P and cell are now empty.

If this latest code will work with that, were probably done. If you didn't plan on that, you may still need to change the code to delete the remaining cell formula in the adjacent Q cell.

Thanks for your help,

Art
 

artz

Well-known Member
Joined
Aug 11, 2002
Messages
791
Hi Rick,

I just tried your code without deleting entries in column Q. Worked perfectly. You are the champ! :)

Thanks again Rick for jumping in here,

Art
 

artz

Well-known Member
Joined
Aug 11, 2002
Messages
791
Hi Rick,

I just tried your code with a tester. What is see that I didn't suggest was a way that the macro runs after one or more of the ticker pairs are deleted. Am guessing that this requires an intervention to tell the macro "done", update the columns. One way would would be to update the list immediately when any two tickers are deleted.

Do you have any ideas how to do this?

Thanks,

Art
 

Watch MrExcel Video

Forum statistics

Threads
1,122,232
Messages
5,594,956
Members
413,954
Latest member
mrsandy

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top