Sheet automatically condenses data??

[Guest]

I want items that are brought over from a checklist on another sheet to
automatically condense togethor so that there are no blank rows in between.
Is this possible??

 

[Guest]

Without knowing the layout of your two sheets you may be able to adapt the
following:
Assume the check list is on Sheet2 and your "Master-list" is on Sheet1
Assume Sheet2 Col A is where the user marks (in some way - say T/F , "X" or
whatever) that s/he wants to choose the item in Col B.
Assume you have a Cmd Button on Sheet2 that the user clicks after completing
the selections.

The code behind this Cmd Button would be something like:

Sub CopySelectionsToSheet1()
Dim oCell as Range
dim CkRng as Range
Dim NewRowCell as Range

Set CkRng = Sheets("Sheet2").Range("A2:A500") ' adj to wherever the
check marks may be
Set NewRowCell =
Sheets("Sheet1").cells(Rows.Count,1).End(xlUp).Offset(1,0)

For each oCell in CkRng
If oCell.Value = "X" Then 'adj to your checkmark
oCell.Copy NewRowCell
Set NewRowCell = NewRowCell.Offset(1,0)
End IF
Next oCell
Set NewRowCell=Nothing
Set oCell=Nothing
Set CkRng = Nothing
End Sub

 

[Tom Ogilvy]

go to the destination sheet,

select column A

Do Edit=>Goto=>Special and select blank Cells

Hit delete and select entire row.

 

[Guest]

Thanks
This might be a stupid question, but how do I create a button on a sheet??

 

[Tom Ogilvy]

view => toolbars, select the forms toolbar. Then click on the button icon
on the forms toolbar and

drag a button to your sheet.

assign the macro to it.

 

[Guest]

On the desired worksheet click: View>Toolbars>Forms
This will display a toolbar menu with several icons.
Click on the one that looks like a small grey button and drag it to
someplace on your worksheet. Select a corner to resize it . Start typing and
the Caption word will be replaced with your caption. Right click and select
Assign macro to assign your (previously made macro) to this button, or click

 

[Guest]

Nevermind,
It was a stupid question.
Same place as I got the checkboxes.

 

[Guest]

This doesn't seem to be working.
Doesn't this just move the item to the same spot as the check box??
I'm already doing that.
I need to get the sheet to condense all of the checked items so that there
are no blank rows.

 

[Tom Ogilvy]

It should do what you ask.