Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Determining number of Ranges
I'm new to VBA and I have a worksheet where I need to determine the number of
contiguous blocks of cells with data in them. There could be two to four blocks. All of the blocks will be within Range A1:I25. I thought I could use Areas.Count so I set up a test Sub with three blocks of cells. Here is the sub: Sub CountAreas() Range("A1:C7").Value = 9 Range("F9:I16").Value = 10 Range("A21:D25").Value = 12 Range("A1:I25").Select x = Selection.Areas.Count Range("A30").Value = x Debug.Print x End Sub The value of x is 1 when I run the sub. I thought it should be 3. Any help on what I'm doing incorrectly would be apprciated. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Determining number of Ranges
Jimmy,
Can't tell you the best way to get but I can suggest why you're getting the answer you're getting....I'm sure someone will jump in here and give you a one liner or two to accomplish your goal. In your macro, your macro does not test for data, it only tests to see if that one Range is valid. Check this out by clearing all data and commenting out the Range values in your macro. You'll get the same answer...."1". You need to loope through each Range, testing for data. Add 1 to a variable that starts at "0" for each range that has data, then check the variable.value, you'll find the answer will be three. HTH, and if it doesn't, like I said, someone will put us both straight....:) Don "Jimmy O" wrote: I'm new to VBA and I have a worksheet where I need to determine the number of contiguous blocks of cells with data in them. There could be two to four blocks. All of the blocks will be within Range A1:I25. I thought I could use Areas.Count so I set up a test Sub with three blocks of cells. Here is the sub: Sub CountAreas() Range("A1:C7").Value = 9 Range("F9:I16").Value = 10 Range("A21:D25").Value = 12 Range("A1:I25").Select x = Selection.Areas.Count Range("A30").Value = x Debug.Print x End Sub The value of x is 1 when I run the sub. I thought it should be 3. Any help on what I'm doing incorrectly would be apprciated. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Determining number of Ranges
Sub CountAreas()
Range("A1:C7").Value = 9 Range("F9:I16").Value = 10 Range("A21:D25").Value = 12 Union(Range("A1:C7"), Range("F9:I16"), Range("A21:D25")).Select x = Selection.Areas.Count Range("A30").Value = x Debug.Print x End Sub You code made one big range / area. -- Gary''s Student - gsnu200771 "Jimmy O" wrote: I'm new to VBA and I have a worksheet where I need to determine the number of contiguous blocks of cells with data in them. There could be two to four blocks. All of the blocks will be within Range A1:I25. I thought I could use Areas.Count so I set up a test Sub with three blocks of cells. Here is the sub: Sub CountAreas() Range("A1:C7").Value = 9 Range("F9:I16").Value = 10 Range("A21:D25").Value = 12 Range("A1:I25").Select x = Selection.Areas.Count Range("A30").Value = x Debug.Print x End Sub The value of x is 1 when I run the sub. I thought it should be 3. Any help on what I'm doing incorrectly would be apprciated. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Determining number of Ranges
I'm not quite sure why you're selecting stuff, but here's another option:
Range("A1:C7,F9:I16,A21:D25").Select MsgBox Selection.Areas.Count Jimmy O wrote: I'm new to VBA and I have a worksheet where I need to determine the number of contiguous blocks of cells with data in them. There could be two to four blocks. All of the blocks will be within Range A1:I25. I thought I could use Areas.Count so I set up a test Sub with three blocks of cells. Here is the sub: Sub CountAreas() Range("A1:C7").Value = 9 Range("F9:I16").Value = 10 Range("A21:D25").Value = 12 Range("A1:I25").Select x = Selection.Areas.Count Range("A30").Value = x Debug.Print x End Sub The value of x is 1 when I run the sub. I thought it should be 3. Any help on what I'm doing incorrectly would be apprciated. -- Dave Peterson |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Determining number of Ranges
Dave,
If I'm reading the OP correctly he has a number of ranges defined, there may or may not be data in a cell or cells in any of these ranges. I think what he's trying to develop is the number of ranges that do have data in at least one cell. Might be wrong and hopefully he'll jump in here and clarify a bit..... Don "Dave Peterson" wrote: I'm not quite sure why you're selecting stuff, but here's another option: Range("A1:C7,F9:I16,A21:D25").Select MsgBox Selection.Areas.Count Jimmy O wrote: I'm new to VBA and I have a worksheet where I need to determine the number of contiguous blocks of cells with data in them. There could be two to four blocks. All of the blocks will be within Range A1:I25. I thought I could use Areas.Count so I set up a test Sub with three blocks of cells. Here is the sub: Sub CountAreas() Range("A1:C7").Value = 9 Range("F9:I16").Value = 10 Range("A21:D25").Value = 12 Range("A1:I25").Select x = Selection.Areas.Count Range("A30").Value = x Debug.Print x End Sub The value of x is 1 when I run the sub. I thought it should be 3. Any help on what I'm doing incorrectly would be apprciated. -- Dave Peterson |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Determining number of Ranges
Thanks Don. I know the block of cells will have data. See my reply to your
2nd post for more info that I believe Dave asked for. "Don" wrote: Jimmy, Can't tell you the best way to get but I can suggest why you're getting the answer you're getting....I'm sure someone will jump in here and give you a one liner or two to accomplish your goal. In your macro, your macro does not test for data, it only tests to see if that one Range is valid. Check this out by clearing all data and commenting out the Range values in your macro. You'll get the same answer...."1". You need to loope through each Range, testing for data. Add 1 to a variable that starts at "0" for each range that has data, then check the variable.value, you'll find the answer will be three. HTH, and if it doesn't, like I said, someone will put us both straight....:) Don "Jimmy O" wrote: I'm new to VBA and I have a worksheet where I need to determine the number of contiguous blocks of cells with data in them. There could be two to four blocks. All of the blocks will be within Range A1:I25. I thought I could use Areas.Count so I set up a test Sub with three blocks of cells. Here is the sub: Sub CountAreas() Range("A1:C7").Value = 9 Range("F9:I16").Value = 10 Range("A21:D25").Value = 12 Range("A1:I25").Select x = Selection.Areas.Count Range("A30").Value = x Debug.Print x End Sub The value of x is 1 when I run the sub. I thought it should be 3. Any help on what I'm doing incorrectly would be apprciated. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Determining number of Ranges
Thank you Gary for the reply. The blocks of numbers could be in other Ranges
of cells, for example B3: D8. However, to get this to work, I may have to designate the Ranges that contain the date and then use Union. "Gary''s Student" wrote: Sub CountAreas() Range("A1:C7").Value = 9 Range("F9:I16").Value = 10 Range("A21:D25").Value = 12 Union(Range("A1:C7"), Range("F9:I16"), Range("A21:D25")).Select x = Selection.Areas.Count Range("A30").Value = x Debug.Print x End Sub You code made one big range / area. -- Gary''s Student - gsnu200771 "Jimmy O" wrote: I'm new to VBA and I have a worksheet where I need to determine the number of contiguous blocks of cells with data in them. There could be two to four blocks. All of the blocks will be within Range A1:I25. I thought I could use Areas.Count so I set up a test Sub with three blocks of cells. Here is the sub: Sub CountAreas() Range("A1:C7").Value = 9 Range("F9:I16").Value = 10 Range("A21:D25").Value = 12 Range("A1:I25").Select x = Selection.Areas.Count Range("A30").Value = x Debug.Print x End Sub The value of x is 1 when I run the sub. I thought it should be 3. Any help on what I'm doing incorrectly would be apprciated. |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Determining number of Ranges
My initial Sub was
Sub CountAreas() x = Range("A1:I25").Areas.Count Range("A30").Value = x Debug.Print x End Sub with the the data already in the three ranges. I added the code to insert the values into the cells and select stuff because I thought I was doing something incorrectly. The above code gives x the value of 1 also. I thought the areas collection count would give the number of non contiguous cells (containing data) within a given Range. Thanks everyone for your responses "Don" wrote: Dave, If I'm reading the OP correctly he has a number of ranges defined, there may or may not be data in a cell or cells in any of these ranges. I think what he's trying to develop is the number of ranges that do have data in at least one cell. Might be wrong and hopefully he'll jump in here and clarify a bit..... Don "Dave Peterson" wrote: I'm not quite sure why you're selecting stuff, but here's another option: Range("A1:C7,F9:I16,A21:D25").Select MsgBox Selection.Areas.Count Jimmy O wrote: I'm new to VBA and I have a worksheet where I need to determine the number of contiguous blocks of cells with data in them. There could be two to four blocks. All of the blocks will be within Range A1:I25. I thought I could use Areas.Count so I set up a test Sub with three blocks of cells. Here is the sub: Sub CountAreas() Range("A1:C7").Value = 9 Range("F9:I16").Value = 10 Range("A21:D25").Value = 12 Range("A1:I25").Select x = Selection.Areas.Count Range("A30").Value = x Debug.Print x End Sub The value of x is 1 when I run the sub. I thought it should be 3. Any help on what I'm doing incorrectly would be apprciated. -- Dave Peterson |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Determining number of Ranges
Thank you very much for the reply Dave. I explain a little more why I
selected stuff in my reply to Don's 2nd post. the data won't always be in the three ranges I designated. one set of data could be in be in B9:E13, for example. Thank you again. "Dave Peterson" wrote: I'm not quite sure why you're selecting stuff, but here's another option: Range("A1:C7,F9:I16,A21:D25").Select MsgBox Selection.Areas.Count Jimmy O wrote: I'm new to VBA and I have a worksheet where I need to determine the number of contiguous blocks of cells with data in them. There could be two to four blocks. All of the blocks will be within Range A1:I25. I thought I could use Areas.Count so I set up a test Sub with three blocks of cells. Here is the sub: Sub CountAreas() Range("A1:C7").Value = 9 Range("F9:I16").Value = 10 Range("A21:D25").Value = 12 Range("A1:I25").Select x = Selection.Areas.Count Range("A30").Value = x Debug.Print x End Sub The value of x is 1 when I run the sub. I thought it should be 3. Any help on what I'm doing incorrectly would be apprciated. -- Dave Peterson |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Determining number of Ranges
Jimmy,
Still don't know if I understand correctly what you're trying to do, but if I do, this code with some revisions to suit your set up will give you the number of Ranges that have at least one cell with data in it..... Option Explicit Sub NumOfRges() Dim Rge1 As Range Dim Rge2 As Range Dim Cell As Range Dim x1, x2 As Integer x1 = 0 x2 = 0 Set Rge1 = Range("A1:D7") Set Rge2 = Range("C17:D22") For Each Cell In Rge1 If Cell.Value < "" Then x1 = 1 End If Next For Each Cell In Rge2 If Cell.Value < "" Then x2 = 1 End If Next MsgBox ("x1 = ") & x1 MsgBox ("x2 = ") & x2 MsgBox ("Total Ranges with Data = ") & x1 + x2 End Sub Probably a cleaner way to get there but this does work. You will have to define each range within the macro and loop through each rge. HTH, Don "Jimmy O" wrote: My initial Sub was Sub CountAreas() x = Range("A1:I25").Areas.Count Range("A30").Value = x Debug.Print x End Sub with the the data already in the three ranges. I added the code to insert the values into the cells and select stuff because I thought I was doing something incorrectly. The above code gives x the value of 1 also. I thought the areas collection count would give the number of non contiguous cells (containing data) within a given Range. Thanks everyone for your responses "Don" wrote: Dave, If I'm reading the OP correctly he has a number of ranges defined, there may or may not be data in a cell or cells in any of these ranges. I think what he's trying to develop is the number of ranges that do have data in at least one cell. Might be wrong and hopefully he'll jump in here and clarify a bit..... Don "Dave Peterson" wrote: I'm not quite sure why you're selecting stuff, but here's another option: Range("A1:C7,F9:I16,A21:D25").Select MsgBox Selection.Areas.Count Jimmy O wrote: I'm new to VBA and I have a worksheet where I need to determine the number of contiguous blocks of cells with data in them. There could be two to four blocks. All of the blocks will be within Range A1:I25. I thought I could use Areas.Count so I set up a test Sub with three blocks of cells. Here is the sub: Sub CountAreas() Range("A1:C7").Value = 9 Range("F9:I16").Value = 10 Range("A21:D25").Value = 12 Range("A1:I25").Select x = Selection.Areas.Count Range("A30").Value = x Debug.Print x End Sub The value of x is 1 when I run the sub. I thought it should be 3. Any help on what I'm doing incorrectly would be apprciated. -- Dave Peterson |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Determining number of Ranges
If the filled in cells all contain constants (no formulas) and the other cells
in that range (A1:I25) are empty, how about: Option Explicit Sub CountAreas() Dim myRng As Range Dim myArea As Range Set myRng = Nothing On Error Resume Next Set myRng = ActiveSheet.Range("a1:i25").Cells _ .SpecialCells(xlCellTypeConstants) On Error GoTo 0 MsgBox myRng.Areas.Count For Each myArea In myRng.Areas MsgBox myArea.Address(0, 0) Next myArea End Sub But the areas that excel uses may not be the same as the areas that you would have used. Jimmy O wrote: My initial Sub was Sub CountAreas() x = Range("A1:I25").Areas.Count Range("A30").Value = x Debug.Print x End Sub with the the data already in the three ranges. I added the code to insert the values into the cells and select stuff because I thought I was doing something incorrectly. The above code gives x the value of 1 also. I thought the areas collection count would give the number of non contiguous cells (containing data) within a given Range. Thanks everyone for your responses "Don" wrote: Dave, If I'm reading the OP correctly he has a number of ranges defined, there may or may not be data in a cell or cells in any of these ranges. I think what he's trying to develop is the number of ranges that do have data in at least one cell. Might be wrong and hopefully he'll jump in here and clarify a bit..... Don "Dave Peterson" wrote: I'm not quite sure why you're selecting stuff, but here's another option: Range("A1:C7,F9:I16,A21:D25").Select MsgBox Selection.Areas.Count Jimmy O wrote: I'm new to VBA and I have a worksheet where I need to determine the number of contiguous blocks of cells with data in them. There could be two to four blocks. All of the blocks will be within Range A1:I25. I thought I could use Areas.Count so I set up a test Sub with three blocks of cells. Here is the sub: Sub CountAreas() Range("A1:C7").Value = 9 Range("F9:I16").Value = 10 Range("A21:D25").Value = 12 Range("A1:I25").Select x = Selection.Areas.Count Range("A30").Value = x Debug.Print x End Sub The value of x is 1 when I run the sub. I thought it should be 3. Any help on what I'm doing incorrectly would be apprciated. -- Dave Peterson -- Dave Peterson |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
Determining number of Ranges
Thanks for the input. What I was trying to do was take a Range and determine
how many groups of data were within that range. the groups of data could be anywhere in the range. In my case, the Range was A1:I25 and it may look like this within the range: 9 9 9 9 9 9 9 9 9 12 12 12 12 12 12 12 12 14 14 14 14 14 14 14 14 14 14 14 14 I thought Range("A1:I25").Areas.count would give me the answer of 3. I think I will have to specify where the ranges have to be within A1:I25 and then test to see if they're empty. I apologize for being so confusing. Jimmy O "Don" wrote: Jimmy, Still don't know if I understand correctly what you're trying to do, but if I do, this code with some revisions to suit your set up will give you the number of Ranges that have at least one cell with data in it..... Option Explicit Sub NumOfRges() Dim Rge1 As Range Dim Rge2 As Range Dim Cell As Range Dim x1, x2 As Integer x1 = 0 x2 = 0 Set Rge1 = Range("A1:D7") Set Rge2 = Range("C17:D22") For Each Cell In Rge1 If Cell.Value < "" Then x1 = 1 End If Next For Each Cell In Rge2 If Cell.Value < "" Then x2 = 1 End If Next MsgBox ("x1 = ") & x1 MsgBox ("x2 = ") & x2 MsgBox ("Total Ranges with Data = ") & x1 + x2 End Sub Probably a cleaner way to get there but this does work. You will have to define each range within the macro and loop through each rge. HTH, Don "Jimmy O" wrote: My initial Sub was Sub CountAreas() x = Range("A1:I25").Areas.Count Range("A30").Value = x Debug.Print x End Sub with the the data already in the three ranges. I added the code to insert the values into the cells and select stuff because I thought I was doing something incorrectly. The above code gives x the value of 1 also. I thought the areas collection count would give the number of non contiguous cells (containing data) within a given Range. Thanks everyone for your responses "Don" wrote: Dave, If I'm reading the OP correctly he has a number of ranges defined, there may or may not be data in a cell or cells in any of these ranges. I think what he's trying to develop is the number of ranges that do have data in at least one cell. Might be wrong and hopefully he'll jump in here and clarify a bit..... Don "Dave Peterson" wrote: I'm not quite sure why you're selecting stuff, but here's another option: Range("A1:C7,F9:I16,A21:D25").Select MsgBox Selection.Areas.Count Jimmy O wrote: I'm new to VBA and I have a worksheet where I need to determine the number of contiguous blocks of cells with data in them. There could be two to four blocks. All of the blocks will be within Range A1:I25. I thought I could use Areas.Count so I set up a test Sub with three blocks of cells. Here is the sub: Sub CountAreas() Range("A1:C7").Value = 9 Range("F9:I16").Value = 10 Range("A21:D25").Value = 12 Range("A1:I25").Select x = Selection.Areas.Count Range("A30").Value = x Debug.Print x End Sub The value of x is 1 when I run the sub. I thought it should be 3. Any help on what I'm doing incorrectly would be apprciated. -- Dave Peterson |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
Determining number of Ranges
Thank you very much Dave, that worked perfectly.
"Dave Peterson" wrote: If the filled in cells all contain constants (no formulas) and the other cells in that range (A1:I25) are empty, how about: Option Explicit Sub CountAreas() Dim myRng As Range Dim myArea As Range Set myRng = Nothing On Error Resume Next Set myRng = ActiveSheet.Range("a1:i25").Cells _ .SpecialCells(xlCellTypeConstants) On Error GoTo 0 MsgBox myRng.Areas.Count For Each myArea In myRng.Areas MsgBox myArea.Address(0, 0) Next myArea End Sub But the areas that excel uses may not be the same as the areas that you would have used. Jimmy O wrote: My initial Sub was Sub CountAreas() x = Range("A1:I25").Areas.Count Range("A30").Value = x Debug.Print x End Sub with the the data already in the three ranges. I added the code to insert the values into the cells and select stuff because I thought I was doing something incorrectly. The above code gives x the value of 1 also. I thought the areas collection count would give the number of non contiguous cells (containing data) within a given Range. Thanks everyone for your responses "Don" wrote: Dave, If I'm reading the OP correctly he has a number of ranges defined, there may or may not be data in a cell or cells in any of these ranges. I think what he's trying to develop is the number of ranges that do have data in at least one cell. Might be wrong and hopefully he'll jump in here and clarify a bit..... Don "Dave Peterson" wrote: I'm not quite sure why you're selecting stuff, but here's another option: Range("A1:C7,F9:I16,A21:D25").Select MsgBox Selection.Areas.Count Jimmy O wrote: I'm new to VBA and I have a worksheet where I need to determine the number of contiguous blocks of cells with data in them. There could be two to four blocks. All of the blocks will be within Range A1:I25. I thought I could use Areas.Count so I set up a test Sub with three blocks of cells. Here is the sub: Sub CountAreas() Range("A1:C7").Value = 9 Range("F9:I16").Value = 10 Range("A21:D25").Value = 12 Range("A1:I25").Select x = Selection.Areas.Count Range("A30").Value = x Debug.Print x End Sub The value of x is 1 when I run the sub. I thought it should be 3. Any help on what I'm doing incorrectly would be apprciated. -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Determining number of columns of 2 different ranges | Excel Programming | |||
need help determining a row number | Excel Programming | |||
Determining if two ranges overlap | Excel Programming | |||
Determining new category number | Excel Programming | |||
Determining Row Number | Excel Programming |