Shifting rows down in a table

[Howard]

I submitted this little code to a user and when he tried it I out found outthat the range he was using this code on is a table, "error can't shift rows in a table" is the result.

I seldom work with tables and was unaware of this problem.
Range("C7:F7) is named FourBy, he wants to enter data in that range and runthe code and insert that data at the top of the list that is accumulating just below Range FourBy. So whatever he enters goes to the top of the listand FourBy is cleared and C7 is selected ready for the next entry.

Along with not able to shift rows in a table I find if I use FourBy in the
....CountA(Range("C7:F7")) it doesn't fly, but in the Else part it is okay.

I tried to NOT use Select but cannot seem to escape the three Selects in the code. Could not make With Range("FourBy")... and With cells(4, 7)...workfor me.

Any ideas?

Option Explicit

Sub CopyFourByA()
If WorksheetFunction.CountA(Range("C7:F7")) = 0 Then
MsgBox "Nothing to copy!"
Exit Sub
Else
Range("FourBy").Select
Selection.Copy
Range("C7").Select
Selection.Insert Shift:=xlDown
Range("FourBy").ClearContents
Range("C7").Select
End If
End Sub

Thank.
Howard

 

[Claus Busch]

Hi Howard,

Am Fri, 5 Apr 2013 01:03:25 -0700 (PDT) schrieb Howard:

I seldom work with tables and was unaware of this problem.
Range("C7:F7) is named FourBy, he wants to enter data in that range and run the code and insert that data at the top of the list that is accumulating just below Range FourBy. So whatever he enters goes to the top of the list and FourBy is cleared and C7 is selected ready for the next entry.

Along with not able to shift rows in a table I find if I use FourBy in the
...CountA(Range("C7:F7")) it doesn't fly, but in the Else part it is okay.

have a look to the name manager. With every row you insert, FourBy will
wander down. FourBy is name of range C7:F7 at start. You insert one row
and Fourby is name of range C8:F8.
Try:

Sub CopyFourByA()
If WorksheetFunction.CountA(Range("C7:F7")) = 0 Then
MsgBox "Nothing to copy!"
Exit Sub
Else
Rows(8).Insert shift:=xlDown
Range("C7:F7").Cut _
Range("C8:F8")
Range("C7").Select
End If
End Sub


Regards
Claus Busch

 

[Howard]

have a look to the name manager. With every row you insert, FourBy will

wander down. FourBy is name of range C7:F7 at start. You insert one row

and Fourby is name of range C8:F8.

Try:



Sub CopyFourByA()

If WorksheetFunction.CountA(Range("C7:F7")) = 0 Then

MsgBox "Nothing to copy!"

Exit Sub

Else

Rows(8).Insert shift:=xlDown

Range("C7:F7").Cut _

Range("C8:F8")

Range("C7").Select

End If

End Sub





Regards

Claus Busch

--

Win XP PRof SP2 / Vista Ultimate SP2

Office 2003 SP2 /2007 Ultimate SP2

Thanks Claus, nice touch!

Regards,
Howard