ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Discussion (Misc queries) (https://www.excelbanter.com/excel-discussion-misc-queries/)
-   -   Insert Blank Row at a Position Based on Value? (https://www.excelbanter.com/excel-discussion-misc-queries/241149-insert-blank-row-position-based-value.html)

[email protected]

Insert Blank Row at a Position Based on Value?
 
Hi,

I'm tring to automate a whole load of steps with macro's, but have got
stuck with this one :-

I have a large worksheet sorted by column A, which is number of
transactions. I need to insert a blank row at the point where the
number of transactions is above 499. Easy to do manually, but I can't
figure how to do this in a macro. This is a repetive process where the
original data varies daily, so this position won't always be at the
same row. Any ideas ?
--
Rich
http://www.rhodes-pefkos.co.uk

Mike H

Insert Blank Row at a Position Based on Value?
 
Hi,

Do you mean you want to find the number 499 in column A and insert a row
after that? If so try this

Rows(WorksheetFunction.Match(499, Range("A:A")) + 1).EntireRow.Insert

Mike

" wrote:

Hi,

I'm tring to automate a whole load of steps with macro's, but have got
stuck with this one :-

I have a large worksheet sorted by column A, which is number of
transactions. I need to insert a blank row at the point where the
number of transactions is above 499. Easy to do manually, but I can't
figure how to do this in a macro. This is a repetive process where the
original data varies daily, so this position won't always be at the
same row. Any ideas ?
--
Rich
http://www.rhodes-pefkos.co.uk


Lars-Åke Aspelin[_2_]

Insert Blank Row at a Position Based on Value?
 
On Sun, 30 Aug 2009 09:30:34 -0700 (PDT),
"
wrote:

Hi,

I'm tring to automate a whole load of steps with macro's, but have got
stuck with this one :-

I have a large worksheet sorted by column A, which is number of
transactions. I need to insert a blank row at the point where the
number of transactions is above 499. Easy to do manually, but I can't
figure how to do this in a macro. This is a repetive process where the
original data varies daily, so this position won't always be at the
same row. Any ideas ?


Try this macro:

Sub insert_row_after_499()
r = 1
With ActiveSheet
While .Cells(r, "A") < "" And .Cells(r, "A") <= 499
r = r + 1
Wend
.Cells(r, "A").EntireRow.Insert
End With
End Sub

Hope this helps / Lars-Åke


All times are GMT +1. The time now is 03:46 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com