Macro for moving entire rows in Excell?

[DW]

Hi:

In working with rows of data in Excel, I often need to move them to the bottom of the page. This is something that I repeat often as I'm finished with that row and want to get it out of the way but not delete it.

SO...is there a way to set up a Macro/Button to do this, greying out the text and moving the highlighted row automatically to the next blank row on the list???

Thanks...DW

 

[Carl Witthoft]

Hi:

In working with rows of data in Excel, I often need to move them to the
bottom of the page. This is something that I repeat often as I'm finished
with that row and want to get it out of the way but not delete it.

SO...is there a way to set up a Macro/Button to do this, greying out the text
and moving the highlighted row automatically to the next blank row on the
list???

Thanks...DW

yes... I don't think I can do it off the top of my head, but it's
certainly easy enough to dig up the commands to grey the row, cut the
row, and insert the row. Probably the easiest way to find the "next
blank row" is to put some sort of tag like a cell that says "TheEnd" in
it, and doing a Row Insert (rather than Paste) just below that location.

 

[Carl Witthoft]

Hi:

In working with rows of data in Excel, I often need to move them to the
bottom of the page. This is something that I repeat often as I'm finished
with that row and want to get it out of the way but not delete it.

SO...is there a way to set up a Macro/Button to do this, greying out the text
and moving the highlighted row automatically to the next blank row on the
list???

Thanks...DW

Ooops. there's another way: create a column that is blank to begin
with, but you type something (like maybe "done") when you're done with a
row. Then after finishing with a few rows, just do a sort on that
column.

 

[JE McGimpsey]

Hi:

In working with rows of data in Excel, I often need to move them to the
bottom of the page. This is something that I repeat often as I'm finished
with that row and want to get it out of the way but not delete it.

SO...is there a way to set up a Macro/Button to do this, greying out the text
and moving the highlighted row automatically to the next blank row on the
list???

One way (with very minimal error checking, and assuming XL04 or earlier):

Attach this to a forms button or a toolbar macro button:

Public Sub GreyAndMoveDown()
Dim rCopy As Range
Dim nMyGrey As Long
nMyGrey = RGB(192, 192, 192)

If ActiveWindow.SelectedSheets.Count > 1 Then
MsgBox "UnGroup worksheets before running this macro."
ElseIf Not TypeOf Selection Is Range Then
MsgBox "Select one or more cells in the rows to move."
Else
If Selection.Areas.Count > 1 Then
MsgBox "Selection must be continuous."
Else
With ActiveSheet
With .Range(.Cells(1, 1), _
.Cells(.Rows.Count, 1).End(xlUp)).EntireRow
Set rCopy = Intersect( _
.Cells, Selection.EntireRow)
If Not rCopy Is Nothing Then
With .Cells(.Rows.Count + 1, 1).Resize( _
rCopy.Rows.Count).EntireRow
rCopy.Copy Destination:=.Cells
.Font.Color = nMyGrey
End With
rCopy.Delete
End If
End With
End With
End If
End If
End Sub