question on setting Range

mtnbiker419

question on setting Range

Excel16
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/

-

Aechelon

Registered User

Thu Oct 23 15:42:42 CDT 2003

Re:question on setting Range

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/

-

anonymous

Registered User

Thu Oct 23 17:03:09 CDT 2003

Re:question on setting Range

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.

Quote

-----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/

.

-