Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
csimont
 
Posts: n/a
Default Can I develop a macro to add blank rows to a list in Excel?

I have a list containing thousands of rows to which I would like to add blank
rows between varied groups of rows. I've tried developing macros to make
quick work of this tedious job but I don't know the statement I need to
develop to work similiar to a subtotal action. The point at which I would
like to add a row is when there is a change in a certain column. I do not
need a subtotal but I do need a blank line added at every change. Is this
even possible or do I add each blank line manually using shortcuts on my
keyboard?
  #2   Report Post  
Posted to microsoft.public.excel.misc
Kevin B
 
Posts: n/a
Default Can I develop a macro to add blank rows to a list in Excel?


The subroutine that follows assumes that the data you are working with
resides in Sheet1, that the values that determine line insertions are in
column H and that the data has been previously sorted by column H

Sub InsertLine()

Dim wb As Workbook
Dim ws As Worksheet
Dim varVal1 As Variant
Dim varVal2 As Variant

Set wb = ActiveWorkbook
Set ws = wb.Worksheets("Sheet1")
ws.Activate
Range("H1").Select

varVal1 = ActiveCell.Value
varVal2 = ActiveCell.Offset(1).Value

Do Until varVal1 = ""
If varVal1 < varVal2 Then
ActiveCell.Offset(1).Select
Selection.EntireRow.Insert
End If
ActiveCell.Offset(1).Select
varVal1 = ActiveCell.Value
varVal2 = ActiveCell.Offset(1).Value
Loop

Set wb = Nothing
Set ws = Nothing

End Sub

--
Kevin Backmann


"csimont" wrote:

I have a list containing thousands of rows to which I would like to add blank
rows between varied groups of rows. I've tried developing macros to make
quick work of this tedious job but I don't know the statement I need to
develop to work similiar to a subtotal action. The point at which I would
like to add a row is when there is a change in a certain column. I do not
need a subtotal but I do need a blank line added at every change. Is this
even possible or do I add each blank line manually using shortcuts on my
keyboard?

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
Inserting blank rows in Excel. kwm5321 Excel Discussion (Misc queries) 3 December 1st 06 01:19 AM
Deleting rows in a macro in Excel THEFALLGUY Excel Discussion (Misc queries) 4 December 23rd 05 02:59 PM
TRYING TO SET UP EXCEL SPREADSHEET ON MY COMPUTER MEGTOM New Users to Excel 5 October 27th 05 03:06 AM
Conditional list without blank rows Andrew Excel Worksheet Functions 1 June 9th 05 03:28 PM
How do I remove blank rows in Excel? m28leics Excel Discussion (Misc queries) 2 November 30th 04 12:56 AM


All times are GMT +1. The time now is 06:52 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"