Clean up blanks in columns

artz

Well-known Member
Joined
Aug 11, 2002
Messages
830
Office Version
  1. 2016
Platform
  1. Windows
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
 

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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)?
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,513
Messages
6,114,072
Members
448,546
Latest member
KH Consulting

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
Back
Top