variable ranges using VBA

luvbite38 · Aug 22, 2011

Hi,

Can someone please help me with following situation:

I have to sum a range, but the issue is the range will always be variable. see example below:
A B
1 x 1
2 x 1
3 x 1 < here I want a macro to select the range of all the x in col A 4 y 1
5 y 1 < here I want a macro to select the range of all the Y in col A
6 z 1 < here I want a macro to select the range of all the Z in col A
7 p 1 < here I want a macro to select the range of all the p in col A

How can i get macro select the variable range. I dont wanna name the ranges, as there may be 100s of diffirent types of items in column A.

please help

Kind Regards

Peter_SSs · Aug 22, 2011

luvbite38 said:
Hi,

Can someone please help me with following situation:

I have to sum a range, but the issue is the range will always be variable. see example below:
A B
1 x 1
2 x 1
3 x 1 < here I want a macro to select the range of all the x in col A 4 y 1
5 y 1 < here I want a macro to select the range of all the Y in col A
6 z 1 < here I want a macro to select the range of all the Z in col A
7 p 1 < here I want a macro to select the range of all the p in col A

How can i get macro select the variable range. I dont wanna name the ranges, as there may be 100s of diffirent types of items in column A.

please help

Kind Regards

You say you want to sum the ranges, but then you say you want the macro to select a range. What is it you are really trying to do?

Is the data sorted so that all the equal column A values are together?

If not, would it be alright to sort it like that?

Peter_SSs · Aug 22, 2011

Also, does the data have a header row in row 1?

luvbite38 · Aug 22, 2011

Thanks Peter,

Basically, I have written a macro to insert a blank row everytime the value is changed in column A. Now using macro, I want to sum the ranges of each item in column A.

I want something like this:

A B
1 x 1
2 x 1
3 x 1
sub-total = 3
5 y 1

sub-total = 1
6 z 1 <
sub-total = 1
7 p 1 <
sub-total = 1
Total = 6

hope it makes sense?

luvbite38 · Aug 22, 2011

yes the first four lines are header.

once again, you help is much appreciated.

Regards.
A

Peter_SSs · Aug 22, 2011

Sounds like you are trying to re-invent the wheel. Excel already has a subtotal feature that does this. You could just apply that manually, or if you really need a macro ..

 Sub ST() Range("A4", Range("A" & Rows.Count).End(xlUp)).Resize(, 2) _ .Subtotal GroupBy:=1, Function:=xlSum, TotalList:=Array(2), _ Replace:=True, PageBreaks:=False, SummaryBelowData:=True End Sub

luvbite38 · Aug 23, 2011

Thanks a million Peter,

Is there a possibility, I can send you my workbook for you to have a quick look at?

I actually trying to achieve something else, and part of it to define the variable ranges?

I am sorry for being pain.

Regards,

luvbite38 · Aug 23, 2011

Hope this makes more sense?

I am really very thankful for sparing time to help me here.

Kind Regards,
A

luvbite38 · Aug 23, 2011

Macro used is as follow:

Sub Summary2()

Application.ScreenUpdating = False

' delete the row if see sub-total

Dim r As Long, mcol As String, i As Long
Dim ChkRange As Range
Set ChkRange = Range("A:A")
Dim cell As Range
For Each cell In ChkRange
If cell = "Sub-Total" Then
cell.EntireRow.Delete
End If
Next

r = Cells(Rows.Count, "B").End(xlUp).Row

mcol = Cells(r, 1).Value

For i = r To 4 Step -1
If Cells(i, 1).Value <> mcol Then
mcol = Cells(i, 1).Value
Rows(i + 1).Insert
With Cells(i + 1, 1)
.Value = "Sub-Total"
.Offset(0, 2).Value = "@ Dia 12"
.Offset(0, 4).Value = "@ Dia 16"
.Offset(0, 6).Value = "@ Dia 20"
.Offset(0, 8).Value = "@ Dia 24"
.Offset(0, 10).Value = "@ Dia 28"
.Offset(0, 12).Value = "@ Dia 32"
.Offset(0, 14).Value = "@ Dia 36"
.Offset(0, 16).Value = "@ Dia 40"
End With
Range("A1048576").Select
Selection.End(xlUp).Select
Selection.Offset(1, 0).Select
With ActiveCell
.Value = "Sub-Total"
.Offset(0, 2).Value = "@ Dia 12"
.Offset(0, 4).Value = "@ Dia 16"
.Offset(0, 6).Value = "@ Dia 20"
.Offset(0, 8).Value = "@ Dia 24"
.Offset(0, 10).Value = "@ Dia 28"
.Offset(0, 12).Value = "@ Dia 32"
.Offset(0, 14).Value = "@ Dia 36"
.Offset(0, 16).Value = "@ Dia 40"
End With
End If
Next i
Selection.EntireRow.Delete
Application.ScreenUpdating = True
End Sub

Peter_SSs · Aug 23, 2011

I know I haven't answered your question about summing a variable range, but see if this does what you want. Test in a copy of your workbook. Check that In have data in correct rows/columns or a slight tweak will be needed.

Excel Workbook

A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

P

Q

R

3

ID

Items

BAR DIA

SHAPE

SEQ NO.

BAR GRADE

BAR DIMENSIONS

PIN DIA (P)

LAPS

NO

BAR LENGTH

TOTAL MASS

4

A

B

C

E

F

L

NO

TYPE

REQ

5

1

A

12

1

6

1

A

12

1

7

1

A

40

1

8

1

A

40

1

9

2

B

12

2

10

2

B

12

2

11

2

B

16

2

12

2

B

40

2

13

3

C

40

3

14

3

C

40

3

15

3

C

40

3

16

3

C

40

3

17

SubTotals (Before)

Excel Workbook

A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

P

Q

R

3

ID

Items

BAR DIA

SHAPE

SEQ NO.

BAR GRADE

BAR DIMENSIONS

PIN DIA (P)

LAPS

NO

BAR LENGTH

TOTAL MASS

4

A

B

C

E

F

L

NO

TYPE

REQ

5

1

A

12

1

6

1

A

12

1

7

1

A

40

1

8

1

A

40

1

9

Sub-Total

4

@ Dia 12

2

@ Dia 16

0

@ Dia 20

0

@ Dia 24

0

@ Dia 28

0

@ Dia 32

0

@ Dia 36

0

@ Dia 40

2

10

2

B

12

2

11

2

B

12

2

12

2

B

16

2

13

2

B

40

2

14

Sub-Total

8

@ Dia 12

4

@ Dia 16

2

@ Dia 20

0

@ Dia 24

0

@ Dia 28

0

@ Dia 32

0

@ Dia 36

0

@ Dia 40

2

15

3

C

40

3

16

3

C

40

3

17

3

C

40

3

18

3

C

40

3

19

Sub-Total

12

@ Dia 12

0

@ Dia 16

0

@ Dia 20

0

@ Dia 24

0

@ Dia 28

0

@ Dia 32

0

@ Dia 36

0

@ Dia 40

12

20

SubTotals (After)

Sub STot() Dim a, st, strw, t Dim i As Long, lr As Long, c As Long st = Array("Sub-Total", 1, "@ Dia 12", 12, "@ Dia 16", 16, _ "@ Dia 20", 20, "@ Dia 24", 24, "@ Dia 28", 28, _ "@ Dia 32", 32, "@ Dia 36", 36, "@ Dia 40", 40) Application.ScreenUpdating = False ReDim t(1 To 40) As Long With Range("A4", Range("A" & Rows.Count).End(xlUp)).Resize(, 18) Application.DisplayAlerts = False .Subtotal GroupBy:=1, Function:=xlSum, TotalList:=Array(2), _ Replace:=True, PageBreaks:=False, SummaryBelowData:=True Application.DisplayAlerts = True lr = Range("A" & Rows.Count).End(xlUp).Row - 1 a = .Offset(-.Row + 1).Resize(lr).Value i = .Row Do i = i + 1 If a(i, 3) = "" Then ReDim strw(LBound(st) To UBound(st)) For c = LBound(st) To UBound(st) If IsNumeric(st(c)) Then strw(c) = t(st(c)) Else strw(c) = st(c) End If Next c ReDim t(1 To 40) As Long With Cells(i, 1).Resize(, 18) .Font.Bold = False .RowHeight = 25 .Value = strw End With Else t(a(i, 3)) = t(a(i, 3)) + a(i, 18) t(1) = t(1) + a(i, 18) End If Loop While i < lr .EntireColumn.AutoFit .Resize(lr).RemoveSubtotal End With Application.ScreenUpdating = True End Sub 

I hope that is at least close to what you are after.

variable ranges using VBA

luvbite38

Active Member

Excel Facts

Peter_SSs

MrExcel MVP, Moderator

Peter_SSs

MrExcel MVP, Moderator

luvbite38

Active Member

luvbite38

Active Member

Peter_SSs

MrExcel MVP, Moderator

luvbite38

Active Member

luvbite38

Active Member

luvbite38

Active Member

Peter_SSs

MrExcel MVP, Moderator

Similar threads

Forum statistics

Share this page

variable ranges using VBA

Active Member

Excel Facts

MrExcel MVP, Moderator

MrExcel MVP, Moderator

Active Member

Active Member

MrExcel MVP, Moderator

Active Member

Active Member

Active Member

MrExcel MVP, Moderator

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock