Condensing data in new sheet by removing blank rows

**Ghost Of Casper** · 06-24-2013, 04:47 PM

Hello,

I'm trying to condense my information by removing blank rows while keeping the order that the information is displayed.

the sheet I'm pulling from has information in cells based on a Vlookup formula, however it reports it in the same row as on the sheet the array is on. I would like to take that info and condense it on another sheet fro faster reference.

Example
Column a has the numbers used for vlookup while column c has the result for the vlookup. there are empty spaces between results. I would like to take these empty spaces out and have just the returned data show on a new sheet for quick reference.

**alansidman** · 06-24-2013, 04:55 PM

Here is some code to delete all blank rows in a spreadsheet

Sub DeleteBlankRows()
'Deletes the entire row within the selection if _
the ENTIRE row contains no data.

Dim lastrow As Integer
Dim MyRow As Integer
Application.ScreenUpdating = False
lastrow = ActiveSheet.UsedRange.Row - 1 + _
ActiveSheet.UsedRange.Rows.Count
For MyRow = lastrow To 1 Step -1
If Application.CountA(Rows(MyRow)) = 0 Then Rows(MyRow).Delete
Next MyRow
Application.ScreenUpdating = True

End Sub

**Ghost Of Casper** · 06-24-2013, 05:27 PM

Thank you for your input, However I really don't want to delete rows as the workbook i'm creating will change based on the data I input so i'm trying to just use simple formulas so that I can adjust them depending on the needs

**alansidman** · 06-26-2013, 09:07 PM

I don't understand your follow up comment. In your original statement you stated

I'm trying to condense my information by removing blank rows while keeping the order that the information is displayed.

If you don't delete the row, what other choice is there for "removing" a blank row? What do you want to happen with the blank row?

If you still have not solved this issue, suggest you upload a sample worksheet with current structure and what you want structure to look like after "removing" blank rows.

Alan

**Ghost Of Casper** · 06-27-2013, 01:54 PM

Conditional book 2 - Update.xlsxSorry for the confusion, attached is an updated doc showing (hopefully) how I would like to have the data look when finalized. It's not so much as removing or deleting rows but more of omitting them and moving the data to the first available "blank" cell in the column. I have the logic in my head on how it should work but getting the formulas to behave the same is a little tricky for me

**alansidman** · 06-27-2013, 05:54 PM

Because you have formulas in each cell, there are no blanks and my vba would not work in that case. You may want to consider some method of sorting based upon the data returned or employing a helper column to assist in your sort.

Alan