How to add rows(cells) to a named range
 

Hi

I want to: for each cell in a range, if the cell = to the cell above,
then add to range.

How can I do this?


--
Ctech
------------------------------------------------------------------------
Ctech's Profile: http://www.excelforum.com/member.php...o&userid=27745
View this thread: http://www.excelforum.com/showthread...hreadid=473291

How to add rows(cells) to a named range
 

Why not provide an example of the before and after with a representation of
your data.

--
Regards,
Tom Ogilvy

"Ctech" wrote in
message ...

Hi

I want to: for each cell in a range, if the cell = to the cell above,
then add to range.

How can I do this?


--
Ctech
------------------------------------------------------------------------
Ctech's Profile:
http://www.excelforum.com/member.php...o&userid=27745
View this thread: http://www.excelforum.com/showthread...hreadid=473291

How to add rows(cells) to a named range
 

This is the macro Im working on.

What my problem is now is that I can't manage to save a range..

Cut out of macro:

RangeX = Address: ActiveCell.Offset(-1, 0).Address



Whole macro:




Sub DeleteSumTotalZero()
'
' Macro1 Macro
' Macro recorded 04/10/2005 by Taylor Nelson Sofres plc
'

'
Dim Sup As String
Dim CC As Long
Dim DelRg As Range
Dim Cell As Range
Dim Numb As Long
Dim Addres As Range
Dim Output As String
Dim RangeX As Range

Set RangeX = Nothing

Sup = Range("I2").Value
CC = Range("H2").Value
Numb = 1
' Selects the first cell in the cost centre column

Range("H2").Select



' Add next row to range if it is the same CC and suppliers as the ro
above


Do

If ActiveCell.Value = ActiveCell.Offset(-1, 0).Value _
And ActiveCell.Offset(0, 1).Value = ActiveCell.Offset(-1, 1).Valu
Then

ActiveCell.Offset(0, 1).Select
With Selection.Interior
.ColorIndex = 3
.Pattern = xlSolid
End With
ActiveCell.Offset(0, -1).Select
Numb = Numb + 1
ActiveCell.Offset(0, 3) = Numb

Else

' Here I'm trying to mark the Rows which equals each other, b
useing the & numb

Address = ActiveCell.Offset("-" & Numb, "0").Address

CC = ActiveCell.Value
Sup = ActiveCell.Offset(0, 1).Value

' Here is my main problem I want to save the range with the nam
"RangeX" i.e. H2:H5

RangeX = Address: ActiveCell.Offset(-1, 0).Address




' Here I have a problem to, want to ckeck the sum of the numbers i
the range
If RangeX.sum = 0 Then
Range(& RangeX).Select
Selection.EntireRow.Delete X1ToLeft


Numb = 1

End If



ActiveCell.Offset(1, 0).Select

Loop Until IsEmpty(ActiveCell) And IsEmpty(ActiveCell.Offset(0, 1))

Range("H2").Select
TheEnd:
MsgBox ("All Suppliers under Cost centres which adds up to 0 is no
deleted.")


End Su

--
Ctec
-----------------------------------------------------------------------
Ctech's Profile: http://www.excelforum.com/member.php...fo&userid=2774
View this thread: http://www.excelforum.com/showthread.php?threadid=47329

How to add rows(cells) to a named range
 

Im getting closer, however I'm not there yet.. as After one run throught
the "Do", is goes off track.

ANyone see the problem?


Sub DeleteSumTotalZero()
'
' Macro1 Macro
' Macro recorded 04/10/2005 by Taylor Nelson Sofres plc
'

'

Dim DelRg As Range
Dim Cell As Range
Dim Numb As Long

Dim Output As String
Dim RangeX As Range
Dim sName As String
Set RangeX = Nothing


Numb = 0
' Selects the first cell in the cost centre column

Range("H3").Select



' Add next row to range if it is the same CC and suppliers as the row
above


Do

If ActiveCell.Value = ActiveCell.Offset(-1, 0).Value _
And ActiveCell.Offset(0, 1).Value = ActiveCell.Offset(-1, 1).Value
Then

ActiveCell.Offset(0, 1).Select
With Selection.Interior
ColorIndex = 3
Pattern = xlSolid
End With
ActiveCell.Offset(0, -1).Select
Numb = Numb + 1
ActiveCell.Offset(0, 3) = Numb
ActiveCell.Offset(1, 0).Select

Else


Address = ActiveCell.Offset("-" & Numb, 2).Address
AddressAbove = ActiveCell.Offset(-1, 2).Address


Range(Address, AddressAbove).Select

sName = "'" & ActiveSheet.Name & "'!"

Output = Selection.Address( _
External:=False, RowAbsolute:=False, _
ColumnAbsolute:=False) & ")"

Output = Replace(Output, ",", "," & sName)

Output = "=sum(" & sName & Output

ActiveCell.Offset(0, 1) = Output
ActiveCell.Offset(0, 1).Select
AddresseY = ActiveCell.Address





If ActiveCell.Value = 0 Then
ActiveCell.Offset(Numb, -3).Select
Numb = 0
ActiveCell.Offset(1, 0).Select
Else

ActiveCell.Offset(Numb, 0).Select
With Selection.Interior
ColorIndex = 4
Pattern = xlSolid
End With
Numb = 0

End If



End If




Loop Until IsEmpty(ActiveCell) And IsEmpty(ActiveCell.Offset(0, 1))

Range("H2").Select
TheEnd:
MsgBox ("All Suppliers under Cost centres which adds up to 0 is now
deleted.")


End Sub


--
Ctech
------------------------------------------------------------------------
Ctech's Profile: http://www.excelforum.com/member.php...o&userid=27745
View this thread: http://www.excelforum.com/showthread...hreadid=473291

How to add rows(cells) to a named range
 

Based on your stated need, after sorting on these fields, you could get the
grouping/sum using the built in Data=Subtotal function (turn on the macro
recorder while you do it manually to get the code).

Once you have the subtotals miplaced, you could loop through the subtotal
cells and make a decision on whether it is equal to zero or not. Then you
could delete the rows above by using the directpredent property of the cell
containing the subtotal.

--
Regards,
Tom Ogilvy


"Ctech" wrote in
message ...

Im getting closer, however I'm not there yet.. as After one run throught
the "Do", is goes off track.

ANyone see the problem?


Sub DeleteSumTotalZero()
'
' Macro1 Macro
' Macro recorded 04/10/2005 by Taylor Nelson Sofres plc
'

'

Dim DelRg As Range
Dim Cell As Range
Dim Numb As Long

Dim Output As String
Dim RangeX As Range
Dim sName As String
Set RangeX = Nothing


Numb = 0
' Selects the first cell in the cost centre column

Range("H3").Select



' Add next row to range if it is the same CC and suppliers as the row
above


Do

If ActiveCell.Value = ActiveCell.Offset(-1, 0).Value _
And ActiveCell.Offset(0, 1).Value = ActiveCell.Offset(-1, 1).Value
Then

ActiveCell.Offset(0, 1).Select
With Selection.Interior
ColorIndex = 3
Pattern = xlSolid
End With
ActiveCell.Offset(0, -1).Select
Numb = Numb + 1
ActiveCell.Offset(0, 3) = Numb
ActiveCell.Offset(1, 0).Select

Else


Address = ActiveCell.Offset("-" & Numb, 2).Address
AddressAbove = ActiveCell.Offset(-1, 2).Address


Range(Address, AddressAbove).Select

sName = "'" & ActiveSheet.Name & "'!"

Output = Selection.Address( _
External:=False, RowAbsolute:=False, _
ColumnAbsolute:=False) & ")"

Output = Replace(Output, ",", "," & sName)

Output = "=sum(" & sName & Output

ActiveCell.Offset(0, 1) = Output
ActiveCell.Offset(0, 1).Select
AddresseY = ActiveCell.Address





If ActiveCell.Value = 0 Then
ActiveCell.Offset(Numb, -3).Select
Numb = 0
ActiveCell.Offset(1, 0).Select
Else

ActiveCell.Offset(Numb, 0).Select
With Selection.Interior
ColorIndex = 4
Pattern = xlSolid
End With
Numb = 0

End If



End If




Loop Until IsEmpty(ActiveCell) And IsEmpty(ActiveCell.Offset(0, 1))

Range("H2").Select
TheEnd:
MsgBox ("All Suppliers under Cost centres which adds up to 0 is now
deleted.")


End Sub


--
Ctech
------------------------------------------------------------------------
Ctech's Profile:
http://www.excelforum.com/member.php...o&userid=27745
View this thread: http://www.excelforum.com/showthread...hreadid=473291