Excel VBA code help please
Posted by Korrinna on February 08, 2001 5:18 PM
In Excel 2000 I am maintaining a list of data whose records increase every day (the columns and the 4 sorted categories stay the same). I could use some help in adding VBA code that would either insert a blank row in between sorted (but not subtotaled) rows, or alternatively, to double the height of the last row in each sort category and vertically align that row's text to the top. The idea is to make the sorted data easier to read by adding space between the sorted categories. This might be a Do...Loop but I'm unsure. TIA.
Posted by Dave Hawley on February 08, 2001 7:33 PM
Hi Korrinna
This should work. You will need to change the text to look for ("Head*") to something that will match your headings. You may also need to change the column to look in.
Sub tryThis()
Dim Found As Range
Dim StopCell As Range
With Columns(1)
Set Found = Range("A1")
Set StopCell = .Find(What:="Head*", _
After:=.Cells(65536, 1), SearchDirection:=xlPrevious)
Do Until Found = StopCell
Set Found = .Find(What:="Head*", _
After:=Found.Offset(2, 0), SearchDirection:=xlNext)
Found.EntireRow.Insert
Loop
End With
End Sub
Hope it helps
OzGrid Business Applications
Posted by Korrinna on February 08, 2001 8:13 PM
Thank you Dave but something not right
Do Until Found = StopCell Set Found = .Find(What:="Head*", _ After:=Found.Offset(2, 0), SearchDirection:=xlNext) Found.EntireRow.Insert Loop
Thanks Dave...when I tried the procedure it kept looping as it added row after row between found header rows until I stopped it manually. Also it added rows between the last row of the found header and the second-to-last row of that same header, instead of between differing header names. But you helped put me on the right track and I'll keep experimenting and hopefully will solve this. Thanks again. Korrinna.
Posted by Dave Hawley on February 08, 2001 9:37 PM
Re: Thank you Dave but something not right
Do Until Found = StopCell Set Found = .Find(What:="Head*", _ After:=Found.Offset(2, 0), SearchDirection:=xlNext) Found.EntireRow.Insert Loop
Hi Korrinna
Here's what I did.
In Column A I put "Head1" then in the next 5 rows beneath that I put some other text and numbers. below that i put "Head2" followed by another 5 o 6 rows of data. I did this for about 50 rows.
Hopefully this may help you, if not let me know.
OzGrid Business Applications
