Sorry about the advertising, but that's how I get this FREE.
since 10 July May, 2002
Last updated 10th July, 2002
DISCLAIMER: I'm not claiming originality here. Some are, some aren't. I'm just
putting them here because they are too handy NOT to be published!
Some handy macros
Condense Rows V1.0
Select a partial column & it concatenates all cells into the first cell.
Sub CondenseRows()
For Each Cell In Selection
CondVal = CondVal & Cell.Value
Cell.Value = ""
Next
Cells(Selection.Row, Selection.Column).Value = CondVal
End Sub
Condense Rows V2.0
Concatenates all selected colum cells into the first cell, adds space & deltes empty rows.
Sub CondenseRows()
For Each Cell In Selection ' This does actual collation into top selected row
CondVal = CondVal & Cell.Value & " "
Cell.Value = ""
Next
Cells(Selection.Row, Selection.Column).Value = CondVal
With Selection ' This changes format to wrap text - dB
.WrapText = True
End With
Rng = Selection.Rows.Count ' This deletes empty rows in selection
ActiveCell.Offset(0, 0).Select
Application.ScreenUpdating = False
For i = 1 To Rng
If ActiveCell.Value = 0 Then 'You can replace "" with 0 to delete rows with 'the value zero
Selection.EntireRow.Delete
Else
ActiveCell.Offset(1, 0).Select
End If
Next i
Application.ScreenUpdating = True
End Sub
Some recommended Excel links