LinkBack Thread Tools Search this Thread Display Modes
Prev Previous Post   Next Post Next
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 73
Default Insert 5 Row if a Certain Value in Column A Q

I want to insert 5 blank rows, above the first time 10000 appears in Column A

Then Subtotal Column E & F (from Row 9 down) on the 2nd Row of the inserted rows, to finish this line off add a Top border to both cells

Finally a simple E-F formula on this Row in Column H

I've found code below, but this inserts a row on each "10000" that appears in Column A (there are multiple rows showing 10000, I just want the first one)

Sub BlankLine()

Dim Col As Variant
Dim BlankRows As Long
Dim LastRow As Long
Dim R As Long
Dim StartRow As Long

Col = "A"
StartRow = 1
BlankRows = 1

LastRow = Cells(Rows.Count, Col).End(xlUp).Row

Application.ScreenUpdating = False

With ActiveSheet
For R = LastRow To StartRow + 1 Step -1
If .Cells(R, Col) = "10000" Then
..Cells(R, Col).EntireRow.Insert Shift:=xlDown
End If
Next R
End With
Application.ScreenUpdating = True

End Sub
 
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 text in column A and calculate the average from column B Sverre Excel Programming 4 April 24th 09 09:07 AM
Insert a text in Column A and calculate the average from column B Sverre Excel Programming 7 April 23rd 09 12:13 PM
insert a blank column between each column in a data file Holly Excel Discussion (Misc queries) 1 October 31st 07 07:04 PM
insert row / insert column command buttons fairgreen Excel Worksheet Functions 1 October 29th 07 02:41 PM
Macro - Insert&Label Column, if the labeled column doesn't exist Jeff[_43_] Excel Programming 1 December 15th 04 09:33 PM


All times are GMT +1. The time now is 04:21 AM.

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"