#1   Report Post  
Junior Member
 
Posts: 1
Default Insert Rows Below

Hello,
I'm trying to insert rows below a row. I first run a filter so that some rows are showing then I want to insert new rows after each row that is showing. I need to manually insert a variable number of rows after each row that is showing.
I used to be able to use the "Insert Row Below" option in the older version of Excel, but this version seems like it doesn't have it?
Thanks in advance for your help
  #2   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 621
Default Insert Rows Below

I have never seen that command in any version of Excel I ever used.

Which "older version" are you speaking of?

Insert Copied Cells is closest thing I can think of.


Gord

On Tue, 5 Jun 2012 01:17:35 +0000, ExcelUser73
wrote:


Hello,
I'm trying to insert rows below a row. I first run a filter so that
some rows are showing then I want to insert new rows after each row that
is showing. I need to manually insert a variable number of rows after
each row that is showing.
I used to be able to use the "Insert Row Below" option in the older
version of Excel, but this version seems like it doesn't have it?
Thanks in advance for your help

  #3   Report Post  
Senior Member
 
Posts: 663
Default

Quote:
Originally Posted by ExcelUser73 View Post
Hello,
I'm trying to insert rows below a row. I first run a filter so that some rows are showing then I want to insert new rows after each row that is showing. I need to manually insert a variable number of rows after each row that is showing.
I used to be able to use the "Insert Row Below" option in the older version of Excel, but this version seems like it doesn't have it?
Thanks in advance for your help
Hi,

I don't remember there being an "Insert Row Below" option in Excel... In Word yes, but Excel??? Hmmm....

Anyway, I'm sure you could do what you need, but you'd need VBA to do it.
Might be worth posing the question again with the view of someone helping you out with code to do this.

Sorry I cannot be of more help than that.

S.
  #4   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 1,522
Default Insert Rows Below

On Monday, June 4, 2012 8:17:35 PM UTC-5, ExcelUser73 wrote:
Hello,
I'm trying to insert rows below a row. I first run a filter so that
some rows are showing then I want to insert new rows after each row that
is showing. I need to manually insert a variable number of rows after
each row that is showing.
I used to be able to use the "Insert Row Below" option in the older
version of Excel, but this version seems like it doesn't have it?
Thanks in advance for your help




--
ExcelUser


???

for each c in activesheet.usedrange.specialcells(xlvisible)
c.entirerow.insert
next c
  #5   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 3,514
Default Insert Rows Below

It happens that ExcelUser73 formulated :
Hello,
I'm trying to insert rows below a row. I first run a filter so that
some rows are showing then I want to insert new rows after each row that
is showing. I need to manually insert a variable number of rows after
each row that is showing.
I used to be able to use the "Insert Row Below" option in the older
version of Excel, but this version seems like it doesn't have it?
Thanks in advance for your help


Like everyone else states, I've never seen such an option in Excel. It
certainly would be nice if there was one, though!

<FWIW
I include a utility in all my addins that gives users the option (from
the right-click menu) to insert rows above or below the active row, OR
left or right of the active column. A prompt to enter the number of
rows/cols (default=1) appears and the deed is done! Perhaps you could
include something similar.

(The popup menu also has an option to insert more rows/cols to extend
the end of a pre-defined table. Same prompt for how many to insert.)

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion




  #6   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 31
Default Insert Rows Below

The following code will do what you want:

Sub InsertRowsBelow()
Dim anyR As Range
Set anyR = Selection.SpecialCells(xlVisible)
Selection.SpecialCells(xlVisible).Offset(1, 0).EntireRow.Insert
anyR.Offset(1, 0).Select
End Sub

If you don't need to select the new rows, then all you need is

Selection.SpecialCells(xlVisible).Offset(1, 0).EntireRow.Insert

You can place the above in yoru personal.xlsx workbook and when you
need to run, just press ALT-F8 and select it.

Robert Flanagan
Add-ins.com LLC
http://www.add-ins.com
Productivity add-ins and downloadable books on VB macros for Excel


On Jun 5, 1:54*pm, GS wrote:
It happens that ExcelUser73 formulated :

Hello,
I'm trying to insert rows below a row. *I first run a filter so that
some rows are showing then I want to insert new rows after each row that
is showing. I need to manually insert a variable number of rows after
each row that is showing.
I used to be able to use the "Insert Row Below" option in the older
version of Excel, but this version seems like it doesn't have it?
Thanks in advance for your help


Like everyone else states, I've never seen such an option in Excel. It
certainly would be nice if there was one, though!

<FWIW
I include a utility in all my addins that gives users the option (from
the right-click menu) to insert rows above or below the active row, OR
left or right of the active column. A prompt to enter the number of
rows/cols (default=1) appears and the deed is done! Perhaps you could
include something similar.

(The popup menu also has an option to insert more rows/cols to extend
the end of a pre-defined table. Same prompt for how many to insert.)

--
Garry

Free usenet access athttp://www.eternal-september.org
ClassicVB Users Regroup!
* * comp.lang.basic.visual.misc
* * microsoft.public.vb.general.discussion


  #7   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 3,514
Default Insert Rows Below

Bob,
I assume you meant this reply for the OP?

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion


  #8   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 2
Default Insert Rows Below

On Wednesday, June 6, 2012 at 1:00:39 PM UTC+1, Bob Flanagan wrote:
The following code will do what you want:

Sub InsertRowsBelow()
Dim anyR As Range
Set anyR = Selection.SpecialCells(xlVisible)
Selection.SpecialCells(xlVisible).Offset(1, 0).EntireRow.Insert
anyR.Offset(1, 0).Select
End Sub

If you don't need to select the new rows, then all you need is

Selection.SpecialCells(xlVisible).Offset(1, 0).EntireRow.Insert

You can place the above in yoru personal.xlsx workbook and when you
need to run, just press ALT-F8 and select it.

Robert Flanagan
Add-ins.com LLC
http://www.add-ins.com
Productivity add-ins and downloadable books on VB macros for Excel


On Jun 5, 1:54*pm, GS wrote:
It happens that ExcelUser73 formulated :

Hello,
I'm trying to insert rows below a row. *I first run a filter so that
some rows are showing then I want to insert new rows after each row that
is showing. I need to manually insert a variable number of rows after
each row that is showing.
I used to be able to use the "Insert Row Below" option in the older
version of Excel, but this version seems like it doesn't have it?
Thanks in advance for your help


Like everyone else states, I've never seen such an option in Excel. It
certainly would be nice if there was one, though!

<FWIW
I include a utility in all my addins that gives users the option (from
the right-click menu) to insert rows above or below the active row, OR
left or right of the active column. A prompt to enter the number of
rows/cols (default=1) appears and the deed is done! Perhaps you could
include something similar.

(The popup menu also has an option to insert more rows/cols to extend
the end of a pre-defined table. Same prompt for how many to insert.)

--
Garry

Free usenet access athttp://www.eternal-september.org
ClassicVB Users Regroup!
* * comp.lang.basic.visual.misc
* * microsoft.public.vb.general.discussion


Bob, your macro works beautifully, thanks! Astonishing that this simple functionality should be available in a Word table, but not in Excel. Although no one in this thread had come across it on older versions of Word, it seems the OP was correct in saying it used to be available: see https://support..office.com/en-us/ar...b-71cb4e5d3e16 (under "Insert a table row or column", point 3).
  #9   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 2
Default Insert Rows Below

On Wednesday, June 6, 2012 at 1:00:39 PM UTC+1, Bob Flanagan wrote:
The following code will do what you want:

Sub InsertRowsBelow()
Dim anyR As Range
Set anyR = Selection.SpecialCells(xlVisible)
Selection.SpecialCells(xlVisible).Offset(1, 0).EntireRow.Insert
anyR.Offset(1, 0).Select
End Sub

If you don't need to select the new rows, then all you need is

Selection.SpecialCells(xlVisible).Offset(1, 0).EntireRow.Insert

You can place the above in yoru personal.xlsx workbook and when you
need to run, just press ALT-F8 and select it.

Robert Flanagan
Add-ins.com LLC
http://www.add-ins.com
Productivity add-ins and downloadable books on VB macros for Excel


On Jun 5, 1:54*pm, GS wrote:
It happens that ExcelUser73 formulated :

Hello,
I'm trying to insert rows below a row. *I first run a filter so that
some rows are showing then I want to insert new rows after each row that
is showing. I need to manually insert a variable number of rows after
each row that is showing.
I used to be able to use the "Insert Row Below" option in the older
version of Excel, but this version seems like it doesn't have it?
Thanks in advance for your help


Like everyone else states, I've never seen such an option in Excel. It
certainly would be nice if there was one, though!

<FWIW
I include a utility in all my addins that gives users the option (from
the right-click menu) to insert rows above or below the active row, OR
left or right of the active column. A prompt to enter the number of
rows/cols (default=1) appears and the deed is done! Perhaps you could
include something similar.

(The popup menu also has an option to insert more rows/cols to extend
the end of a pre-defined table. Same prompt for how many to insert.)

--
Garry

Free usenet access athttp://www.eternal-september.org
ClassicVB Users Regroup!
* * comp.lang.basic.visual.misc
* * microsoft.public.vb.general.discussion


Bob, your macro works beautifully, thanks! Astonishing that this simple functionality should be available in a Word table, but not in Excel. Although no one in this thread had come across it on older versions of Excel, it seems the OP was correct in saying it used to be available: see https://support.office.com/en-us/art...b-71cb4e5d3e16 (under "Insert a table row or column", point 3).
  #10   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 1
Default Insert Rows Below

Am Mittwoch, 6. Juni 2012 14:00:39 UTC+2 schrieb Bob Flanagan:
The following code will do what you want:

Sub InsertRowsBelow()
Dim anyR As Range
Set anyR = Selection.SpecialCells(xlVisible)
Selection.SpecialCells(xlVisible).Offset(1, 0).EntireRow.Insert
anyR.Offset(1, 0).Select
End Sub

If you don't need to select the new rows, then all you need is

Selection.SpecialCells(xlVisible).Offset(1, 0).EntireRow.Insert

You can place the above in yoru personal.xlsx workbook and when you
need to run, just press ALT-F8 and select it.

Robert Flanagan
Add-ins.com LLC
http://www.add-ins.com
Productivity add-ins and downloadable books on VB macros for Excel


On Jun 5, 1:54*pm, GS wrote:
It happens that ExcelUser73 formulated :

Hello,
I'm trying to insert rows below a row. *I first run a filter so that
some rows are showing then I want to insert new rows after each row that
is showing. I need to manually insert a variable number of rows after
each row that is showing.
I used to be able to use the "Insert Row Below" option in the older
version of Excel, but this version seems like it doesn't have it?
Thanks in advance for your help


Like everyone else states, I've never seen such an option in Excel. It
certainly would be nice if there was one, though!

<FWIW
I include a utility in all my addins that gives users the option (from
the right-click menu) to insert rows above or below the active row, OR
left or right of the active column. A prompt to enter the number of
rows/cols (default=1) appears and the deed is done! Perhaps you could
include something similar.

(The popup menu also has an option to insert more rows/cols to extend
the end of a pre-defined table. Same prompt for how many to insert.)

--
Garry

Free usenet access athttp://www.eternal-september.org
ClassicVB Users Regroup!
* * comp.lang.basic.visual.misc
* * microsoft.public.vb.general.discussion


Bob, great solution. Many thanks!
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
insert rows and copying cells in column b,c, and d into new rows gbpg Excel Programming 0 December 31st 09 01:34 AM
VBA to count rows from specific cell and insert rows Valerie Excel Programming 3 November 26th 07 11:14 PM
Insert rows: Formats & formulas extended to additonal rows Twishlist Excel Worksheet Functions 0 October 22nd 07 04:23 AM
Insert page breaks every 50 rows but do not include hidden rows Martin[_21_] Excel Programming 5 March 12th 07 06:10 PM
How do i insert of spacer rows between rows in large spreadsheets laurel Excel Discussion (Misc queries) 0 April 24th 06 01:38 PM


All times are GMT +1. The time now is 06:38 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"