Condense Data

[lacogada]

Would like to make a selection then condense the data.

Information on the sheets shown in image below is input from
other sheets in the workbook.

Not sure if this makes a difference but the first column of the selections
have conditional formating to where nothing shows until greater than zero
is filled in.

Though the image below has a space between the subject and first iten in list,
that is not needed.

 

[mumps]

There are two ways that could be done. One way would be to delete the empty cells in the first column of the selection moving up everything below. The other is by hiding the blank rows. Which would work for you best?

 

[lacogada]

There are two ways that could be done. One way would be to delete the empty cells in the first column of the selection moving up everything below. The other is by hiding the blank rows. Which would work for you best?

Delete.
Thanks

 

[lacogada]

There are two ways that could be done. One way would be to delete the empty cells in the first column of the selection moving up everything below. The other is by hiding the blank rows. Which would work for you best?

delete the empty cells in the first column of the selection moving up everything

 

[mumps]

Try the following: Select the range A161:B183 and run this macro:

Sub lacogada()
    Application.ScreenUpdating = False
    Dim x As Long, rng As Range
    Dim fRow As Long, lRow As Long, col As Long
    col = Selection.Columns(1).Column
    fRow = Selection.Rows(1).Row
    lRow = Selection.Rows.Count + fRow - 1
    For x = lRow To fRow Step -1
        If Cells(x, col) = "" Or Cells(x, col) = 0 Then
            Cells(x, col).Resize(, 2).Delete
        End If
    Next x
    Cells(fRow, col).Select
    Application.ScreenUpdating = True
End Sub

 

[lacogada]

Excellent ... thank you so much ... this will make things so much easier and save me some time.

Is there a way to have it move cells in selected area only ... nothing below it ?

 

[mumps]

You are very welcome.

 

[lacogada]

You are very welcome.

Is there a way to have the macro move cells in selected area only ... nothing that would be below selected area ?

 

[mumps]

Try:

Sub lacogada()
    Application.ScreenUpdating = False
    Dim x As Long, y As Long, rng As Range
    Dim fRow As Long, lRow As Long, col As Long
    col = Selection.Columns(1).Column
    fRow = Selection.Rows(1).Row
    lRow = Selection.Rows.Count + fRow - 1
    For x = lRow To fRow Step -1
        If Cells(x, col) = "" Or Cells(x, col) = 0 Then
            Cells(x, col).Resize(, 2).Delete
            y = y + 1
        End If
    Next x
    lRow = Range(Cells(fRow, col), Cells(lRow, col)).Find("", SearchOrder:=xlByRows, SearchDirection:=xlNext).Row
    Cells(lRow, col).Resize(y).Insert shift:=xlDown
    Cells(lRow, col + 1).Resize(y).Insert shift:=xlDown
    Cells(fRow, col).Select
    Application.ScreenUpdating = True
End Sub

 

[lacogada]

Again ... excellent ... appreciate that mumps.

 

[mumps]

My pleasure.