This should cover ALL contingencies as if finds the last row, finds the 1st and
last columns to evaluate and the row to insert.
Sub insertrowsifSAS()
Dim lr As Long
Dim c As Long
Dim I As Long
lr = ActiveSheet.UsedRange.SpecialCells(xlCellTypeLastCell).Row
'finds the first column to evaluate
For c = 1 To Cells(1, Columns.Count).End(xlToLeft).Column
If Val(Cells(1, c)) > 0 Then
fc = c
Exit For
End If
Next c
For I = fc To Cells(1, Columns.Count).End(xlToLeft).Column
wr = Cells(3, I).End(xlDown).Row
if wr > 2 And wr < lr Then Cells(wr, 1).Resize(Cells(1, I).EntireRow.Insert
‘If wr > 2 And wr < lr Then Cells(wr, 1).Resize(Int(Cells(1, I)) +
1).EntireRow.Insert
Next I
End Sub
Don Guillett
Microsoft Excel Developer
SalesAid Software
dguille...@gmail.com
From: vijay yadav
Sent: Saturday, July 07, 2012 6:35 AM
To: excel-macros@googlegroups.com
Subject: Re: $$Excel-Macros$$ Re: to insert rows ,rows inserted should be equal
to integer in corresponding cell in the top row
In simple words i need to read column number which has a entry .
say J column has a entry
read value in J1 cell
say J1 has "3"
enter "3"number of rows
Continue this step for all rows
Yes,you undesrtood right Don Guillett ,but your macro is not working on
original sheet.
I have attached sheet ,Please help.
Regards
Vijay yadav
On Friday, 6 July 2012 19:48:47 UTC+5:30, Don Guillett wrote:
If ? I understand your need try this
Sub insertrowsifSAS()
dim I as long
For i = 2 To Cells(1, Columns.Count).End(xlToLeft).Column
lr = Cells(Rows.Count, i).End(xlUp).Row
If lr > 1 Then Cells(lr, 1).Resize(Int(Cells(1, i)) + 1).EntireRow.Insert
Next i
End Sub
Don Guillett
Microsoft Excel Developer
SalesAid Software
dguille...@gmail.com
From: vijay yadav
Sent: Friday, July 06, 2012 2:00 AM
To: excel-macros@googlegroups.com
Subject: $$Excel-Macros$$ Re: to insert rows ,rows inserted should be equal
to integer in corresponding cell in the top row
On Friday, 6 July 2012 12:29:14 UTC+5:30, vijay yadav wrote:
Hi Experts,
I need a macro which coluld do the following
1)top row contains integer 1 to 50 from B column to BZ column
2)other rows contain entries in column A
3)Also rows contains one entry beneath the integers in top row
the macro should read the columm corresponding to entries in column A and
should insert no of rows below entries
the no of rows inserted should be equal to the integer in top row
Please see excel sheet attached for reference
Please help
Regards
Vijay
--
FORUM RULES (986+ members already BANNED for violation)
1) Use concise, accurate thread titles. Poor thread titles, like Please Help,
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get
quick attention or may not be answered.
2) Don't post a question in the thread of another member.
3) Don't post questions regarding breaking or bypassing any security measure.
4) Acknowledge the responses you receive, good or bad.
5) Cross-promotion of, or links to, forums competitive to this forum in
signatures are prohibited.
NOTE : Don't ever post personal or confidential data in a workbook. Forum
owners and members are not responsible for any loss.
------------------------------------------------------------------------------------------------------
To post to this group, send email to excel-macros@googlegroups.com
To unsubscribe, send a blank email to
excel-macros+unsubscr...@googlegroups.com
--
FORUM RULES (986+ members already BANNED for violation)
1) Use concise, accurate thread titles. Poor thread titles, like Please Help,
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get
quick attention or may not be answered.
2) Don't post a question in the thread of another member.
3) Don't post questions regarding breaking or bypassing any security measure.
4) Acknowledge the responses you receive, good or bad.
5) Cross-promotion of, or links to, forums competitive to this forum in
signatures are prohibited.
NOTE : Don't ever post personal or confidential data in a workbook. Forum
owners and members are not responsible for any loss.
------------------------------------------------------------------------------------------------------
To post to this group, send email to excel-macros@googlegroups.com
To unsubscribe, send a blank email to excel-macros+unsubscr...@googlegroups.com
--
FORUM RULES (986+ members already BANNED for violation)
1) Use concise, accurate thread titles. Poor thread titles, like Please Help,
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get
quick attention or may not be answered.
2) Don't post a question in the thread of another member.
3) Don't post questions regarding breaking or bypassing any security measure.
4) Acknowledge the responses you receive, good or bad.
5) Cross-promotion of, or links to, forums competitive to this forum in
signatures are prohibited.
NOTE : Don't ever post personal or confidential data in a workbook. Forum
owners and members are not responsible for any loss.
------------------------------------------------------------------------------------------------------
To post to this group, send email to excel-macros@googlegroups.com
To unsubscribe, send a blank email to excel-macros+unsubscr...@googlegroups.com
