Cutting rows and inserting in another sheet

[Guest]

Is there a way to set up Excel to delete the blank row that is created when a
row is cut and pasted to another sheet?

Thanks

 

[Guest]

I am not sure of a fast and easy way, but what I usually do is cut and paste
the data and then do a quick sort of the area you are interested in and it
will toss the blank rows at the bottom. You could easily write a macro that
does this.

 

[Gord Dibben]

Karen

Do the cut and paste with a macro and delete the blank row(s)

Macro...........

Sub cut_n_paste()
Dim rng1 As Range
Set rng1 = Selection
rng1.EntireRow.Cut Destination:= _
Worksheets("Sheet2").Range("A1")
rng1.EntireRow.Delete
End Sub


Gord Dibben MS Excel MVP


On Tue, 22 May 2007 17:21:01 -0700, Karen Bates <Karen

 

[Gord Dibben]

Here is something a little more generic.

Sub cut_n_paste()
Dim rng1 As Range
Dim rng2 as Range
Set rng1 = Selection
Set rng2 = Application.InputBox(Prompt:= _
"Select A Cell to Paste to", Type:=8)
rng1.EntireRow.Cut Destination:=rng2
rng1.EntireRow.Delete
End Sub


Gord

 

[Guest]

This one mostly works except that the row that gets deleted isn't the blank
row, it is the row number on the source spreadsheet that corresponds to the
row number on the destination sheet. In other words, I cut row 10 on Sheet 1
and pasted to row 27 on Sheet 2. The row that ends up getting deleted is row
27 on Sheet 1.

Thanks!

 

[Gord Dibben]

With the "generic" code posted I get the cut and paste OK to sheet2 but the
Sheet1 cut row is left blank.

Change to this below seems to do OK.

Sub cut_n_paste22()
Dim rng1 As Range
Dim rng2 As Range
Set rng1 = Selection
Set rng2 = Application.InputBox(Prompt:= _
"Select A Cell to Paste to", Type:=8)
rng1.EntireRow.Copy Destination:=rng2
rng1.EntireRow.Delete
End Sub


Gord