Question on insert lines macro

Monkman

Board Regular
Joined
Jan 16, 2004
Messages
83
Hello
I know this code below inserts blank rows based upon conditions.
Can someone please provide an explanation of this code?

Thank You
Monk



Dim Items As Integer
Dim TempCell As Excel.Range
Range("A2").Select
Do Until ActiveCell.Formula = ""
If ActiveCell.Formula = "BAL" Then
Items = 0
ActiveCell.Range("A2").Select
ElseIf UCase(Left(ActiveCell.Formula, 5)) = "CASE:" Then
Set TempCell = ActiveCell
If Items <> 15 Then
Rows(ActiveCell.Row & ":" & ActiveCell.Row + (14 - Items)).Select
Selection.Insert Shift:=xlDown
TempCell.Range("A2").Select
Else
ActiveCell.Range("A2").Select
End If
Else
Items = Items + 1
ActiveCell.Range("A2").Select
End If
Loop
 

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.
Monkman - can you post a sample of the worksheet that this code was written for? The explanation might make more sense if we could see that.

Basically, the code loops through the cells in column A, starting at A2, inserting rows when it find CASE:, and stopping when it reaches an empty cell. The number of rows inserted depends on the number of rows checked since the last BAL.
 
Upvote 0
USTROT DEMO.xls
ABCDEFG
1CASE1ThanksfortheexplanationCornflakegirl
2Conditions:Justbackfromholiday
3BALtryingtomodifythecodetodothefollowing
4ANS
510
61
76FromConditions:toTime:
82Tryingtoget20linesexactlybetweenA2andA15
99insert6blankrowsafterA14
107
113
124
135
148
15Time:
16ANS
1710Tryimgtogetexactly8linesfromANSinA16toType
181insert3blankrowsafterthe6inA19
196
20Type
21E
22TTryingtoget8linesfromEinA21tothe1stinA23
231stinsert5blankrowsaftertheT
24Case2
25Conditions:RepeatthruuntilablankinColumnA
26BALcouldbeupto50cases
27ANS
Sheet2
 
Upvote 0

Forum statistics

Threads
1,213,534
Messages
6,114,186
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top