Formatting Loop
 

Hi BSII,

Every now and then I must give back to what I have taken, so here is some
help for you.

If you are using ranges you don't want to mix them with selections and vice
versa. Selections are directly affecting the cursor position (selected cell)
on the worksheet, whereas you can think of ranges as being "implied" or
"virtual" selections that do not move the cursor. "Range" is not an easy
concept to grasp when you are starting out, as I remember from my own painful
days of leanring.

You can take two approaches with your macro...

Using selections and your logic, something like this is more on track:

Sub e_Add_Spaces()
'define a long integer variable to store the last row position
Dim rw As Long
'store the last row position
rw = Range("B3", Cells(Rows.Count, "B").End(xlUp)).Row + Range("B3",
Cells(Rows.Count, "B").End(xlUp)).Rows.Count - 1
Range("B1").Select
'loop while the selected row is less than the last row
While Selection.Row < rw
'move down to the end of the data subrange
Selection.End(xlDown).Select
Selection.End(xlDown).Select
'insert the two rows
Selection.EntireRow.Insert
Selection.EntireRow.Insert
'add the two rows to the last row value so we don't stop too soon
rw = rw + 2
Wend
'return to your starting cell
Range("B1").Select
End Sub

However...what this will do is insert two rows BEFORE the LAST row in the
current data subrange, and for all the subsequent data subranges it will
select a row before the FIRST row. So without knowing what you want to do,
but guessing that you really want to insert two blank rows before the FIRSt
row in each subrange, you will want to modify it like this:

Sub e_Add_Spaces()
'define a long integer variable to store the last row position
Dim rw As Long
'store the last row position
rw = Range("B3", Cells(Rows.Count, "B").End(xlUp)).Row + Range("B3",
Cells(Rows.Count, "B").End(xlUp)).Rows.Count - 1
Range("B1").Select
'move down to the START of the FIRST data subrange
Selection.End(xlDown).Select
'loop while the selected row is less than the last row
While Selection.Row < rw
'move down to the START of the NEXT data range
Selection.End(xlDown).Select
Selection.End(xlDown).Select
'insert two blank rows
Selection.EntireRow.Insert
Selection.EntireRow.Insert
'add the two rows to the last row value so we don't stop too soon
rw = rw + 2
Wend
'return to your starting cell
Range("B1").Select
End Sub

Even so, using selections is an inefficient way to code...it will run way
slower than using ranges. A more efficient macro would be this:

Sub e_Add_Spaces_Using_Ranges()
'cursor to cell B1
Range("B1").Select
'define a range and a long integer variable
Dim rng As Range
Dim rw As Long
'store the last row position
rw = Range("B3", Cells(Rows.Count, "B").End(xlUp)).Row + Range("B3",
Cells(Rows.Count, "B").End(xlUp)).Rows.Count - 1
'start at top of range
Set rng = Range("B3")
'if not already at start of first data subrange, loop until it is found
While rng.Cells.Value = "" And rng.Row < rw
'move the range down one row
Set rng = rng.Offset(1)
Wend
'loop while the range row is less than the last row
While rng.Row < rw
'loop until a blank cell is found
While rng.Cells.Value < ""
'move the range down one row
Set rng = rng.Offset(1)
Wend
'loop until a non-blank cell is found and while still above the last row
While rng.Cells.Value = "" And rng.Row < rw
'move the range down one row
Set rng = rng.Offset(1)
Wend
'now you are at the start of the next data subrange
'so insert 2 rows
rng.EntireRow.Insert
rng.EntireRow.Insert
Set rng = rng.Offset(2)
'add two rows to the last row counter so you don't stop too soon
rw = rw + 2
Wend
'clear the range object (good practice is to always clear object variables
at end)
Set rng = Nothing
'we don't have to reposition cursor -- because the selection has not changed
End Sub

Now an even more efficient code would be to use Cells instead of Range, but
I will leave it to you to look that up in Help and learn that at your own
pace.

Let me know if you have any specific questions about the code, i will try to
answer if I have time.

--robin a.k.a. rbyteme


"BSII" wrote:

I am trying to create a macro to format spreadsheet which has a standard
format, but varying length. Theoretically, I'd like to set "B1" as an
origin, then "CNTL+down" twice, add two blank rows, and repeat this process
for the quantity of rows that have data. I am not sure how to create a loop
with only navigation commands where I am not referencing a value of a cell.

I have tried the following:

Sub e_Add_Spaces()

Dim holder As Range
Range("B1").Select
Set rng = Range("B3", Cells(Rows.Count, "B").End(xlUp))
For Each holder In rng
If Not holder.Value = Clear Then
Selection.End(xlDown).Select
Selection.End(xlDown).Select
Selection.EntireRow.Insert
Selection.EntireRow.Insert
End If
Next

Range("B1").Select

End Sub

Formatting Loop
 

heh if i knew a good reference it wouldn't have taken me so many years to
figure all this stuff out on my own, my learning method has mostly been
Multiple Trials and Many Errors. seriously, i know of one that did help me
wrap my brain around the object-oriented language concept (i was late to the
game)...VB in a Nutshell i believe it was called, explained alot of the
concepts fairly well. Had a picture of small furry animal on the cover.

good luck :)



"BSII" wrote:

Thank you very much for your help - I'll give these suggestions a try. As
you say, I'm new to this programming and still getting my arms around the
logic and language. On that note, do you have any suggestions for a good
reference book to help? The online help is OK, but if you are just starting
off, it's not very easy to find some basic answers.

Thanks again.


"rbyteme" wrote:

Hi BSII,

Every now and then I must give back to what I have taken, so here is some
help for you.

If you are using ranges you don't want to mix them with selections and vice
versa. Selections are directly affecting the cursor position (selected cell)
on the worksheet, whereas you can think of ranges as being "implied" or
"virtual" selections that do not move the cursor. "Range" is not an easy
concept to grasp when you are starting out, as I remember from my own painful
days of leanring.

You can take two approaches with your macro...

Using selections and your logic, something like this is more on track:

Sub e_Add_Spaces()
'define a long integer variable to store the last row position
Dim rw As Long
'store the last row position
rw = Range("B3", Cells(Rows.Count, "B").End(xlUp)).Row + Range("B3",
Cells(Rows.Count, "B").End(xlUp)).Rows.Count - 1
Range("B1").Select
'loop while the selected row is less than the last row
While Selection.Row < rw
'move down to the end of the data subrange
Selection.End(xlDown).Select
Selection.End(xlDown).Select
'insert the two rows
Selection.EntireRow.Insert
Selection.EntireRow.Insert
'add the two rows to the last row value so we don't stop too soon
rw = rw + 2
Wend
'return to your starting cell
Range("B1").Select
End Sub

However...what this will do is insert two rows BEFORE the LAST row in the
current data subrange, and for all the subsequent data subranges it will
select a row before the FIRST row. So without knowing what you want to do,
but guessing that you really want to insert two blank rows before the FIRSt
row in each subrange, you will want to modify it like this:

Sub e_Add_Spaces()
'define a long integer variable to store the last row position
Dim rw As Long
'store the last row position
rw = Range("B3", Cells(Rows.Count, "B").End(xlUp)).Row + Range("B3",
Cells(Rows.Count, "B").End(xlUp)).Rows.Count - 1
Range("B1").Select
'move down to the START of the FIRST data subrange
Selection.End(xlDown).Select
'loop while the selected row is less than the last row
While Selection.Row < rw
'move down to the START of the NEXT data range
Selection.End(xlDown).Select
Selection.End(xlDown).Select
'insert two blank rows
Selection.EntireRow.Insert
Selection.EntireRow.Insert
'add the two rows to the last row value so we don't stop too soon
rw = rw + 2
Wend
'return to your starting cell
Range("B1").Select
End Sub

Even so, using selections is an inefficient way to code...it will run way
slower than using ranges. A more efficient macro would be this:

Sub e_Add_Spaces_Using_Ranges()
'cursor to cell B1
Range("B1").Select
'define a range and a long integer variable
Dim rng As Range
Dim rw As Long
'store the last row position
rw = Range("B3", Cells(Rows.Count, "B").End(xlUp)).Row + Range("B3",
Cells(Rows.Count, "B").End(xlUp)).Rows.Count - 1
'start at top of range
Set rng = Range("B3")
'if not already at start of first data subrange, loop until it is found
While rng.Cells.Value = "" And rng.Row < rw
'move the range down one row
Set rng = rng.Offset(1)
Wend
'loop while the range row is less than the last row
While rng.Row < rw
'loop until a blank cell is found
While rng.Cells.Value < ""
'move the range down one row
Set rng = rng.Offset(1)
Wend
'loop until a non-blank cell is found and while still above the last row
While rng.Cells.Value = "" And rng.Row < rw
'move the range down one row
Set rng = rng.Offset(1)
Wend
'now you are at the start of the next data subrange
'so insert 2 rows
rng.EntireRow.Insert
rng.EntireRow.Insert
Set rng = rng.Offset(2)
'add two rows to the last row counter so you don't stop too soon
rw = rw + 2
Wend
'clear the range object (good practice is to always clear object variables
at end)
Set rng = Nothing
'we don't have to reposition cursor -- because the selection has not changed
End Sub

Now an even more efficient code would be to use Cells instead of Range, but
I will leave it to you to look that up in Help and learn that at your own
pace.

Let me know if you have any specific questions about the code, i will try to
answer if I have time.

--robin a.k.a. rbyteme


"BSII" wrote:

I am trying to create a macro to format spreadsheet which has a standard
format, but varying length. Theoretically, I'd like to set "B1" as an
origin, then "CNTL+down" twice, add two blank rows, and repeat this process
for the quantity of rows that have data. I am not sure how to create a loop
with only navigation commands where I am not referencing a value of a cell.

I have tried the following:

Sub e_Add_Spaces()

Dim holder As Range
Range("B1").Select
Set rng = Range("B3", Cells(Rows.Count, "B").End(xlUp))
For Each holder In rng
If Not holder.Value = Clear Then
Selection.End(xlDown).Select
Selection.End(xlDown).Select
Selection.EntireRow.Insert
Selection.EntireRow.Insert
End If
Next

Range("B1").Select

End Sub