Insert Row Macro with Hidden Columns
 

I wrote a macro for users to insert a row below with some (not all
copied cell formulas from above ... the user has to highlight any cel
in the row above where they want the insert and then press a butto
that I have assiciated to the macro ... my macro works except when
have hidden columns (which I do to keep the view simple) ...

I could add a section to pre unhide columns (and then rehide to kee
the view) but I will then lose my active cell pointer (I use this t
make sure I insert the row in the right place) ... any ideas ??? ...
thought I could create a field to hold the activecell reference an
then go back to it once I unhide the columns but have been unable t
figure out how to do this ... the other answer is to understand why th
hidden columns are creating a problem ...

Here is the code I was referencing ...

Private Sub InsertRisk_Click()
ActiveCell.Offset(1, 0).Rows("1:1").EntireRow.Select
Selection.Insert Shift:=xlDown
ActiveCell.Offset(-1, 0).Rows("1:1").EntireRow.Select
Selection.Copy
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveSheet.Paste
Application.CutCopyMode = False

ActiveCell.Range("b1:e1").Select
Selection.ClearContents
ActiveCell.Offset(0, -1).Range("A1").Select

ActiveCell.Range("m1:w1").Select
Selection.ClearContents
ActiveCell.Offset(0, -12).Range("A1").Select

ActiveCell.Range("y1:y1").Select
Selection.ClearContents
ActiveCell.Offset(0, -24).Range("A1").Select

End Sub


Thanks in advanc

--
steven_thoma
-----------------------------------------------------------------------
steven_thomas's Profile: http://www.excelforum.com/member.php...fo&userid=2756
View this thread: http://www.excelforum.com/showthread.php?threadid=47112

Insert Row Macro with Hidden Columns
 

Hi Steven

Try...

Sub test()
ActiveCell.EntireRow.Copy
ActiveCell.EntireRow.Insert Shift:=xlDown

'The following is one line if the text wraps
Application.Intersect(ActiveCell.EntireRow.Offset( 1, 0),
Range("B:E,M:W,Y:Y")).ClearContents

End Sub
--


XL2003
Regards

William



"steven_thomas"
wrote in message
news:steven_thomas.1w0z2k_1127837133.014@excelforu m-nospam.com...

I wrote a macro for users to insert a row below with some (not all)
copied cell formulas from above ... the user has to highlight any cell
in the row above where they want the insert and then press a button
that I have assiciated to the macro ... my macro works except when I
have hidden columns (which I do to keep the view simple) ...

I could add a section to pre unhide columns (and then rehide to keep
the view) but I will then lose my active cell pointer (I use this to
make sure I insert the row in the right place) ... any ideas ??? ... I
thought I could create a field to hold the activecell reference and
then go back to it once I unhide the columns but have been unable to
figure out how to do this ... the other answer is to understand why the
hidden columns are creating a problem ...

Here is the code I was referencing ...

Private Sub InsertRisk_Click()
ActiveCell.Offset(1, 0).Rows("1:1").EntireRow.Select
Selection.Insert Shift:=xlDown
ActiveCell.Offset(-1, 0).Rows("1:1").EntireRow.Select
Selection.Copy
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveSheet.Paste
Application.CutCopyMode = False

ActiveCell.Range("b1:e1").Select
Selection.ClearContents
ActiveCell.Offset(0, -1).Range("A1").Select

ActiveCell.Range("m1:w1").Select
Selection.ClearContents
ActiveCell.Offset(0, -12).Range("A1").Select

ActiveCell.Range("y1:y1").Select
Selection.ClearContents
ActiveCell.Offset(0, -24).Range("A1").Select

End Sub


Thanks in advance


--
steven_thomas
------------------------------------------------------------------------
steven_thomas's Profile:
http://www.excelforum.com/member.php...o&userid=27568
View this thread: http://www.excelforum.com/showthread...hreadid=471122