Table with non-blank and blank data to a table with non-blank data only

[excelisnotmyforte]

Hi excel gurus,

I've been browsing through the forum and have not been able to execute a formula to make this work!!

I want to be able to transfer this table with only the filled in columns to a new table (RPM, Engine, SSG, Hatch, Location will have data inputted by the user, the Time column is the non-variable). Is there a formula that can do this all at once? It can be updated at any time so I would like another table to be the consolidated data set with no blanks. It also does not need to be in chronological order.
The example table is below:

[TABLE="width: 500"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[/TR]
[TR]
[TD]Time[/TD]
[TD]RPM[/TD]
[TD]Engine[/TD]
[TD]SSG[/TD]
[TD]Hatch[/TD]
[TD]Location[/TD]
[/TR]
[TR]
[TD]00:01[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]00:15[/TD]
[TD]600[/TD]
[TD]On[/TD]
[TD]On[/TD]
[TD]Open[/TD]
[TD]ER[/TD]
[/TR]
[TR]
[TD]00:30[/TD]
[TD]1200[/TD]
[TD]On[/TD]
[TD]On[/TD]
[TD]Closed[/TD]
[TD]ER[/TD]
[/TR]
[TR]
[TD]00:45[/TD]
[TD]1200[/TD]
[TD]On[/TD]
[TD]On[/TD]
[TD]Closed[/TD]
[TD]LAZ[/TD]
[/TR]
[TR]
[TD]01:00[/TD]
[TD]0[/TD]
[TD]Off[/TD]
[TD]On[/TD]
[TD]Open[/TD]
[TD]LAZ[/TD]
[/TR]
[TR]
[TD]01:15[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Any help is appreciated!! Thank you!

 

[mumps]

I'm not sure if I understood correctly but try this macro. It assumes the data is in "Sheet1" and places the new data in "Sheet2". Change the sheet names in the code (in red) to suit your needs.

Sub CopyData()
    Application.ScreenUpdating = False
    Sheets("[COLOR="#FF0000"]Sheet2[/COLOR]").UsedRange.ClearContents
    Sheets("[COLOR="#FF0000"]Sheet1[/COLOR]").UsedRange.Copy Sheets("[COLOR="#FF0000"]Sheet2[/COLOR]").Cells(1, 1)
    Sheets("[COLOR="#FF0000"]Sheet2[/COLOR]").Columns("B").SpecialCells(xlCellTypeBlanks).EntireRow.Delete
    Application.ScreenUpdating = True
End Sub

 

[excelisnotmyforte]

Thank you mumps!!

I have a bit of an issue when I run the macro?
I have other sheets that refer to this non-blank data table with formulas and whenever I run the macro, it moves the formulas down one row.... Not sure why its doing this.

 

[mumps]

You said that you wanted the consolidated data set with no blanks so the macro deletes the blank rows. Do these blank rows have formulas?