Stacking (removing blank cells) data without removing rows

[Chemical Engineering Coop]

Hello,
This is my first post but I imagine not my last. The data in which inputs into my excel uses a plug-in medium known as dataPARC. This works in conjunction with the program PARCview in which we use to analyze data from live feeds and the manual data entry of our operators. The issue that is at hand is that when selecting a time period in which to "get data", we get a sheet that has a left hand column of periodic dates descending by hour and columns from left to right of each mill area operator comments. Comments are not made at every hour increment and therefore blanks are formed. This is what I originally get inputting time limits 5/26/11 0:00 to 5/26/11 13:00.
<table style="width: 1081px; height: 469px;" border="0" cellpadding="0" cellspacing="0"><colgroup><col style="mso-width-source:userset;mso-width-alt:3108;width:64pt" width="85"> <col style="mso-width-source:userset;mso-width-alt:7643;width:157pt" width="209"> <col style="mso-width-source:userset;mso-width-alt:7314;width:150pt" width="200"> <col style="mso-width-source:userset;mso-width-alt:5668;width:116pt" width="155"> </colgroup><tbody><tr style="height:12.75pt" height="17"> <td class="xl68" style="height:12.75pt;width:64pt" height="17" width="85">
</td> <td class="xl69" style="border-left:none;width:157pt" width="209"> Recovery Boiler 2</td> <td class="xl68" style="border-left:none;width:150pt" width="200"> Evaporator </td> <td class="xl68" style="border-left:none;width:116pt" width="155">NCG System </td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl68" style="height:12.75pt;border-top:none" height="17">
</td> <td class="xl69" style="border-top:none;border-left:none">LINCOLN.MDE.RB2.Comments.Text
</td> <td class="xl69" style="border-top:none;border-left:none"> LINCOLN.MDE.EVAPS.Comments</td> <td class="xl69" style="border-top:none;border-left:none">LINCOLN.MDE.NCG.Comments</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl70" style="height:12.75pt;border-top:none" align="right" height="17">5/26/2011</td> <td class="xl71" style="border-top:none;border-left:none">
</td> <td class="xl71" style="border-top:none;border-left:none">
</td> <td class="xl71" style="border-top:none;border-left:none">
</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl72" style="height:12.75pt;border-top:none" align="right" height="17">5/26/2011-1:00</td> <td class="xl71" style="border-top:none;border-left:none">
</td> <td class="xl71" style="border-top:none;border-left:none">
</td> <td class="xl71" style="border-top:none;border-left:none">
</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl72" style="height:12.75pt;border-top:none" align="right" height="17">5/26/2011-2:00</td> <td class="xl71" style="border-top:none;border-left:none">Blew down water column and West side</td> <td class="xl71" style="border-top:none;border-left:none">
</td> <td class="xl71" style="border-top:none;border-left:none">
</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl72" style="height:12.75pt;border-top:none" align="right" height="17">5/26/2011-3:00</td> <td class="xl71" style="border-top:none;border-left:none">
</td> <td class="xl71" style="border-top:none;border-left:none">Busy on b/e heater.</td> <td class="xl71" style="border-top:none;border-left:none">
</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl72" style="height:12.75pt;border-top:none" align="right" height="17">5/26/2011-4:00</td> <td class="xl71" style="border-top:none;border-left:none">
</td> <td class="xl71" style="border-top:none;border-left:none">
</td> <td class="xl71" style="border-top:none;border-left:none">
</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl72" style="height:12.75pt;border-top:none" align="right" height="17">5/26/2011-5:00</td> <td class="xl71" style="border-top:none;border-left:none">
</td> <td class="xl71" style="border-top:none;border-left:none">
</td> <td class="xl71" style="border-top:none;border-left:none">
</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl72" style="height:12.75pt;border-top:none" align="right" height="17">5/26/2011-6:00</td> <td class="xl71" style="border-top:none;border-left:none">
</td> <td class="xl71" style="border-top:none;border-left:none">Busy</td> <td class="xl71" style="border-top:none;border-left:none">
</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl72" style="height:12.75pt;border-top:none" align="right" height="17">5/26/2011-7:00</td> <td class="xl71" style="border-top:none;border-left:none">
</td> <td class="xl71" style="border-top:none;border-left:none">Busy </td> <td class="xl71" style="border-top:none;border-left:none">
</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl72" style="height:12.75pt;border-top:none" align="right" height="17">5/26/2011-8:00</td> <td class="xl71" style="border-top:none;border-left:none">
</td> <td class="xl71" style="border-top:none;border-left:none">Pumping out containment too trash can.</td> <td class="xl71" style="border-top:none;border-left:none">
</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl72" style="height:12.75pt;border-top:none" align="right" height="17">5/26/2011-9:00</td> <td class="xl71" style="border-top:none;border-left:none">Busy - Bill V.</td> <td class="xl71" style="border-top:none;border-left:none">
</td> <td class="xl71" style="border-top:none;border-left:none">
</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl72" style="height:12.75pt;border-top:none" align="right" height="17">5/26/2011-10:00</td> <td class="xl71" style="border-top:none;border-left:none">Busy - Changed to 1" beer cans</td> <td class="xl71" style="border-top:none;border-left:none">
</td> <td class="xl71" style="border-top:none;border-left:none">
</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl72" style="height:12.75pt;border-top:none" align="right" height="17">5/26/2011-11:00</td> <td class="xl71" style="border-top:none;border-left:none">
</td> <td class="xl71" style="border-top:none;border-left:none">
</td> <td class="xl71" style="border-top:none;border-left:none">
</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl72" style="height:12.75pt;border-top:none" align="right" height="17">5/26/2011-12:00</td> <td class="xl71" style="border-top:none;border-left:none">
</td> <td class="xl71" style="border-top:none;border-left:none">
</td> <td class="xl71" style="border-top:none;border-left:none">
</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl72" style="height:12.75pt;border-top:none" align="right" height="17">5/26/2011-13:00</td> <td class="xl71" style="border-top:none;border-left:none">
</td> <td class="xl71" style="border-top:none;border-left:none">
</td> <td class="xl71" style="border-top:none;border-left:none">busy</td> </tr> </tbody></table>

What I am looking to do is have a macro (or if not needing a macro just as fine) that would go through and remove the blank cells and stack the text. The date information as well as the comment location must remain with the data (should it be concatenated?). This is what I'd like to have.

<table border="0" cellpadding="0" cellspacing="0" width="813"><colgroup><col style="mso-width-source:userset;mso-width-alt:3108;width:64pt" width="85"> <col style="mso-width-source:userset;mso-width-alt:7643;width:157pt" width="209"> <col style="mso-width-source:userset;mso-width-alt:2889;width:59pt" width="79"> <col style="mso-width-source:userset;mso-width-alt:7314;width:150pt" width="200"> <col style="mso-width-source:userset;mso-width-alt:3108;width:64pt" width="85"> <col style="mso-width-source:userset;mso-width-alt:5668;width:116pt" width="155"> </colgroup><tbody><tr style="height:12.75pt" height="17"> <td class="xl68" style="height:12.75pt;width:64pt" height="17" width="85">
</td> <td style="vertical-align: top;">
</td><td class="xl69" style="border-left:none;width:157pt" width="209">Recovery Boiler 2</td> <td class="xl69" style="border-left:none;width:59pt" width="79">
</td> <td class="xl70" style="border-left:none;width:150pt" width="200">Evaporator </td> <td class="xl70" style="border-left:none;width:64pt" width="85">
</td> <td class="xl70" style="border-left:none;width:116pt" width="155">NCG System </td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl68" style="height:12.75pt;border-top:none" height="17">
</td> <td style="vertical-align: top;">
</td><td class="xl69" style="border-top:none;border-left:none">LINCOLN.MDE.RB2.Comments.Text</td> <td class="xl69" style="border-top:none;border-left:none">
</td> <td class="xl69" style="border-top:none;border-left:none">LINCOLN.MDE.EVAPS.Comments</td> <td class="xl69" style="border-top:none;border-left:none">
</td> <td class="xl69" style="border-top:none;border-left:none">LINCOLN.MDE.NCG.Comments</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl71" style="height:12.75pt;border-top:none" align="right" height="17">5/26/2011-2:00</td> <td style="vertical-align: top;">
</td><td class="xl72" style="border-top:none;border-left:none">Blew down water column and West side</td> <td class="xl73" style="border-top:none;border-left:none" align="right">5/26/2011-3:00</td> <td class="xl72" style="border-top:none;border-left:none">Busy on b/e heater.</td> <td class="xl73" style="border-top:none;border-left:none" align="right">5/26/2011-13:00</td> <td class="xl72" style="border-top:none;border-left:none">busy</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl71" style="height:12.75pt;border-top:none" align="right" height="17">5/26/2011-9:00</td> <td style="vertical-align: top;">
</td><td class="xl72" style="border-top:none;border-left:none">Busy - Bill V.</td> <td class="xl73" style="border-top:none;border-left:none" align="right">5/26/2011-6:00</td> <td class="xl72" style="border-top:none;border-left:none">Busy</td> <td class="xl72" style="border-top:none;border-left:none">
</td> <td class="xl72" style="border-top:none;border-left:none">
</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl71" style="height:12.75pt;border-top:none" align="right" height="17">5/26/2011-10:00</td> <td style="vertical-align: top;">
</td><td class="xl72" style="border-top:none;border-left:none">Busy - Changed to 1" beer cans</td> <td class="xl73" style="border-top:none;border-left:none" align="right">5/26/2011-7:00</td> <td class="xl72" style="border-top:none;border-left:none">Busy </td> <td class="xl72" style="border-top:none;border-left:none">
</td> <td class="xl72" style="border-top:none;border-left:none">
</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl71" style="height:12.75pt;border-top:none" height="17">
</td> <td style="vertical-align: top;">
</td><td class="xl72" style="border-top:none;border-left:none">
</td> <td class="xl73" style="border-top:none;border-left:none" align="right">5/26/2011-8:00</td> <td class="xl72" style="border-top:none;border-left:none">Pumping out containment too trash can.</td> <td class="xl72" style="border-top:none;border-left:none">
</td> <td class="xl72" style="border-top:none;border-left:none">
</td> </tr> </tbody></table>Now the amount of days (period of time) will change based upon whether a user would like to see 1, 2, 3, 4, or 5 days worth of comments and date limits will be inputted. Because of this variability I believe only a macro could be used. My goal is to have an individual just enter the date limits and "vwahla", a easy to read comments sheet.

Any help or direction towards a post in which this issue or the like was resolved would be greatly appreciated.

THANKS!!!

-Sid

P.S.-If more info is needed I will be checking this post periodically so just post what you need and I will reply ASAP.

 

[Ralajer]

The following code assumes that time stamp is in column A and the data starts in row 3 both can be adjusted.

Sub StackComments()
    Dim staRng As Range
    Dim timeRng As Range
    Dim startRow As Long
    Dim endRow As Long
    Dim i As Integer
    Dim numStations As Integer
    Dim timeCol As String
    
    '// Input values based on value position and other values
    numStations = 3
    startRow = 3
    timeCol = "A"
    
    endRow = Range(timeCol & "65536").End(xlUp).Row
    
    Set timeRng = Columns(timeCol & ":" & timeCol)
    '// Copy time columns for each station
    copyTimeCol numStations, timeRng
    
    '// Loop through each station
    For i = 1 To numStations
        '// Creat range for station i
        Set staRng = Range(Cells(startRow, 2 * (i - 1) + timeRng.Column), _
                           Cells(endRow, 2 * (i - 1) + 1 + timeRng.Column))
        '// Call subroutine to check and delete blank comments on a
            '// per station range basis
        deleteBlankComments staRng
    Next i
    
End Sub

Sub copyTimeCol(numStations As Integer, timeRng As Range)
    Dim i As Integer
    
    For i = numStations To 2 Step -1
        '// Copy and insert range between the station columns
        timeRng.Copy
        Columns(i + timeRng.Column).Insert Shift:=xlToRight
    Next i
    Application.CutCopyMode = False
End Sub

Sub deleteBlankComments(rng As Range)
    Dim numRows As Integer
    Dim i As Integer
    
    numRows = rng.Count / 2
    
    '// Loop through the comments for a station from bottom to top
    For i = numRows To 1 Step -1
        '// if the comment is blank then delete the cells
        If rng.Cells(i, 2).Value = Empty Then
            rng.Rows(i).Delete Shift:=xlUp
        End If
    Next i
End Sub

Test against a copy of the data of course. In my testing it worked fine but I may have setup the example data differently than it actually is. Let me know if it works for you.

 

[Roli001]

This would be a quick solutin for a one time fix

A non VBA method would be to filter blank cells in column A. Select from row A3 to last cell (Select A3. Click CTRL+SHIFT+Arrow DOWN). Right click on any selected cell and select Delete Rows.


This would ba a quick VBA method:

Sub deleteRow()

For i = [a65000].End(xlUp).Row To 3 Step -1
    If Cells(i, 1) = "" Then Rows(i).Delete shift:=xlUp
Next i

End Sub

Good luck.

 

[Chemical Engineering Coop]

Ralajer,

Thank you very much for your quick response, I'm setting up the macro now. Instead of just copy and pasting I'm retyping to help solidify my understanding of what is occurring. I will respond ASAP when finished. Thank you again very much!

Sid

 

[Chemical Engineering Coop]

Roli001,

Thank you for your response. I am currently trying Ralajer's method and will attempt yours after I am finished.

Sid

 

[Chemical Engineering Coop]

OK,

I decided that it would be much easier just to concatenate the date/time and operator location and comments all together.

Example:

Recovery Boiler #205/26/2011 2:00Blew down water column and West side
(I wish I knew how to make a space between the #2 and the 05/... date)

Now I just have columns of these concatenated cells in columns based upon location with again a bunch of blanks in between. I am currently trying to remove these blanks in between and have been getting stuck.

Any tips would be greatly appreciated.

Sid

 

[Ralajer]

Sid,

To concatenate a space just add (&" "&) into your formula =A1&" "&B1.

To remove blanks without VBA select the range you want to remove blanks from. Press Ctrl+G or F5 the Goto window will popup press the "Special..." Button select the "Blanks" Checkbox then OK. That will select all the blanks. You can then Delete those cells.