I cant for the life of me figure out how to do this.

I'll attach a sample of the code that i've written so far, just for a
better idea of what i'm talking about.

Basically I would like this bit to count the number of rows with data
in them. Then, move to the next range of data and do the same thing
over again... I cannot find how to write that in the "Range" part. I
can make it work for doing it once because I used "cells" and make it
do 9 iterations, but i would rather it look at the whole row for data,
and then skip to the next area in my program, which is 3 lines down
from this first range of data... I'm not too good at explaining...
Could someone please help me??
Thank you.

Sub row_manipulation()

Dim I As Integer
Dim numRows As Integer
numRows = 0

For I = 2 To 100
numRows = numRows +
Application.WorksheetFunction.CountA(Range(******* )) 'checks to see if
a ninth row (new data)is entered

If numRows = 9 Then
Rows("I:I").Select 'select the first data row and delete
Selection.Delete Shift:=xlUp
Rows("I+8:I+8").Select 'insert new empty row for next time data is
entered
Selection.Insert Shift:=xlDown
End If
I = I + 11 'skip to next analyte
Next I


End Sub



------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~ View and post usenet messages directly from http://www.ExcelForum.com/

create a variable

dim i as integer
i = 9

then in a loop:
.................Range("B" & i)..............

this will check every ninth row or whatever.

I don't know exactly what you are looking for, but maybe you can adapt
this to your purpose.



------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~ View and post usenet messages directly from http://www.ExcelForum.com/

Your question is a little unclear, it sounds like you want
to determine how many rows have data(?). I'll give you an
example of one way, remember a range can be a single cell
or multiple cells. (this will probably word wrap)

Dim oRange as Range, oRangeCell as Range

Set oRange = shtMain.Range("A2", shtMain.Range("A1").End
(xlDown))

For Each oRangeCell in oRange
' do your thing
Next


This example will select all cells in the "A" column from
A2 to the last cell with data in it. Notice when I
use "End" to get the last cell with data that I jumped up
a cell from the one I actually wanted to select, you need
to do this otherwise if A2 (in this example) was the only
cell with data it would not be selected.

You can also play around with the other parameters for End
to expand your range across multiple columns and rows.

I hope that helped.



-----Original Message-----
I cant for the life of me figure out how to do this.

I'll attach a sample of the code that i've written so
far, just for a
better idea of what i'm talking about.

Basically I would like this bit to count the number of
rows with data
in them. Then, move to the next range of data and do the
same thing
over again... I cannot find how to write that in
the "Range" part. I
can make it work for doing it once because I used "cells"
and make it
do 9 iterations, but i would rather it look at the whole
row for data,
and then skip to the next area in my program, which is 3
lines down
from this first range of data... I'm not too good at
explaining...
Could someone please help me??
Thank you.

Sub row_manipulation()

Dim I As Integer
Dim numRows As Integer
numRows = 0

For I = 2 To 100
numRows = numRows +
Application.WorksheetFunction.CountA(Range
(*******)) 'checks to see if
a ninth row (new data)is entered

If numRows = 9 Then
Rows("I:I").Select 'select the first data row and delete
Selection.Delete Shift:=xlUp
Rows("I+8:I+8").Select 'insert new empty row for next
time data is
entered
Selection.Insert Shift:=xlDown
End If
I = I + 11 'skip to next analyte
Next I


End Sub



------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~ View and post usenet messages directly from
http://www.ExcelForum.com/

.

i'll give it a shot, thanks
maybe it would be easier to understand my question if i showed what the
setup of the spreadsheeet looks like.. ?



------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~ View and post usenet messages directly from http://www.ExcelForum.com/

ok, that's a great help, but, does that only search column "B" for data
in the ninth row?

Here's what the spread sheet looks like...

I want to make sure there are only 8 lines of data for each analyte...
so the user enters a ninth row for each analyte, they press the run
macro button, then it searches range C3 to O11.. realizes theres a
ninth row, deletes the row 3, adds a new row under 11(no it would be
10, because it all moved up). Then, it goes to search next range, C14
to O22.. ... keeps up this pattern through the whole spreadsheet.

I just dont know how to write that into the Range() because the way
i've been writing the data into the () is wrong since i keep getting
error msgs.

I thought if i wrote
numRows = numRows + Application.WorksheetFunction.CountA(Range(CI:OI+8 )
where I is a variable that changes in the loop and in this it
represents the row. I know the format in the Range part isnt right at
all .. that's what i need help with....

thank you sooo much if you can give me a hand!

File Attached: http://www.excelforum.com/attachment.php?postid=330425 (samplesheet.xls)

------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~ View and post usenet messages directly from http://www.ExcelForum.com/

numRows = numRows + Application.WorksheetFunction.CountA(Range("C" & I &
":" & "O" & I+8))

I believe this will do for you. Good Luck!



------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~ View and post usenet messages directly from http://www.ExcelForum.com/

YES!! it did.. thank you so much..
now, do you know why i cant use a variable "I", in the Rows("I:I")

what is a way around that.??



------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~ View and post usenet messages directly from http://www.ExcelForum.com/

what are you trying to do with it?



------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~ View and post usenet messages directly from http://www.ExcelForum.com/

well, Rows() is in my If loop.. I know you can write Rows("3:3") if you
want to select row three only, I want to do the same thing, but
everytime the program travels thru the loop it changes the row that it
selects... that's why i would like to use an established variable since
it already has the right number attached to it , but Rows() wont accept
it in the format that i've tried.

I really appreciate your help and time. I'm writing my first program.



------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~ View and post usenet messages directly from http://www.ExcelForum.com/

No problem, believe me, i'm still learning.

If you want to select row 3, just type Rows(3)

if I = 3
all you do is type Rows(I)

and this will give you row 3.



------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~ View and post usenet messages directly from http://www.ExcelForum.com/