+ Reply to Thread
Results 1 to 10 of 10

Deleting contents of cells in non contiguous ranges

  1. #1
    Richard Buttrey
    Guest

    Deleting contents of cells in non contiguous ranges

    Hi,

    I have a workbook with several sheets. Each sheet has several ranges
    of cells containing numbers that need to be erased each month. e.g.
    B30: X50, B70:X90, B130:X150 etc

    At the moment these cells are identified by a specific colour, and I
    have a macro which loops through all the cells from B30 to the last
    cell in column X on each sheet, tests them to see if they are coloured
    with the right colour, and if so deletes the contents of the cell.

    I'm sure this is a very inefficient way of doing the job. Can anyone
    suggest a better approach?

    (Needless to say this is an inherited application, and as the saying
    goes, if I was going there, I wouldn't start from here. It's crying
    out for turning it into a standard database which can be more easily
    manipulated, with reporting tasks feeding off it. Unfortunately I
    don't have the time at the moment to re-organise the whole thing).

    Usual TIA




    __
    Richard Buttrey
    Grappenhall, Cheshire, UK
    __________________________

  2. #2
    Bob Phillips
    Guest

    Re: Deleting contents of cells in non contiguous ranges

    If you don't know in advance which cells are coloured, there seems to be no
    alternative (TINA - shiver!)

    --
    HTH

    Bob Phillips

    (remove nothere from email address if mailing direct)

    "Richard Buttrey" <chaos.theory.nospam.removethis@zen.co.uk> wrote in
    message news:o7iq22181hpde0ojcj1pp57071vqb6akde@4ax.com...
    > Hi,
    >
    > I have a workbook with several sheets. Each sheet has several ranges
    > of cells containing numbers that need to be erased each month. e.g.
    > B30: X50, B70:X90, B130:X150 etc
    >
    > At the moment these cells are identified by a specific colour, and I
    > have a macro which loops through all the cells from B30 to the last
    > cell in column X on each sheet, tests them to see if they are coloured
    > with the right colour, and if so deletes the contents of the cell.
    >
    > I'm sure this is a very inefficient way of doing the job. Can anyone
    > suggest a better approach?
    >
    > (Needless to say this is an inherited application, and as the saying
    > goes, if I was going there, I wouldn't start from here. It's crying
    > out for turning it into a standard database which can be more easily
    > manipulated, with reporting tasks feeding off it. Unfortunately I
    > don't have the time at the moment to re-organise the whole thing).
    >
    > Usual TIA
    >
    >
    >
    >
    > __
    > Richard Buttrey
    > Grappenhall, Cheshire, UK
    > __________________________




  3. #3
    Richard Buttrey
    Guest

    Re: Deleting contents of cells in non contiguous ranges

    On Fri, 31 Mar 2006 16:40:54 +0100, "Bob Phillips"
    <bob.phillips@notheretiscali.co.uk> wrote:

    >If you don't know in advance which cells are coloured, there seems to be no
    >alternative (TINA - shiver!)


    Sorry Bob,

    Maybe didnt explain that well enough.

    The one consistent thing about these ranges is that they always go
    from Col B to Col X and always have the same number of rows - say 20.

    Those cells are always coloured and the macro tests for the colour.

    I was thinking that perhaps building a union of all these cells, and
    then deleting contents in that range object all in one hit might be
    preferable than doing each cell in turn.

    Is there any way of selecting all cells of a specific colour in a
    worksheet? There doesn't appear to be an F5 Special Cells Colour
    option which would be an obvious choice.

    Regards



    __
    Richard Buttrey
    Grappenhall, Cheshire, UK
    __________________________

  4. #4
    Bob Phillips
    Guest

    Re: Deleting contents of cells in non contiguous ranges

    If they are always the same cells, surely the colour is irrelevant. Couldn't
    you just use

    range("B30:X50,B70:X90,B130:X150").ClearContents

    --
    HTH

    Bob Phillips

    (remove nothere from email address if mailing direct)

    "Richard Buttrey" <chaos.theory.nospam.removethis@zen.co.uk> wrote in
    message news:f6uq22h4o2dlmgi4sta02479els7rdrrp9@4ax.com...
    > On Fri, 31 Mar 2006 16:40:54 +0100, "Bob Phillips"
    > <bob.phillips@notheretiscali.co.uk> wrote:
    >
    > >If you don't know in advance which cells are coloured, there seems to be

    no
    > >alternative (TINA - shiver!)

    >
    > Sorry Bob,
    >
    > Maybe didnt explain that well enough.
    >
    > The one consistent thing about these ranges is that they always go
    > from Col B to Col X and always have the same number of rows - say 20.
    >
    > Those cells are always coloured and the macro tests for the colour.
    >
    > I was thinking that perhaps building a union of all these cells, and
    > then deleting contents in that range object all in one hit might be
    > preferable than doing each cell in turn.
    >
    > Is there any way of selecting all cells of a specific colour in a
    > worksheet? There doesn't appear to be an F5 Special Cells Colour
    > option which would be an obvious choice.
    >
    > Regards
    >
    >
    >
    > __
    > Richard Buttrey
    > Grappenhall, Cheshire, UK
    > __________________________




  5. #5
    Richard Buttrey
    Guest

    Re: Deleting contents of cells in non contiguous ranges

    On Fri, 31 Mar 2006 20:55:47 +0100, "Bob Phillips"
    <bob.phillips@notheretiscali.co.uk> wrote:

    >If they are always the same cells, surely the colour is irrelevant. Couldn't
    >you just use
    >
    >range("B30:X50,B70:X90,B130:X150").ClearContents


    Bob,

    Yes I could do that. I was hoping to avoid having to hard code or name
    these ranges and have some sort of generic code.

    There are about 15 sheets with an average of 4 ranges on each sheet.
    The other complication is that from time to time the user needs to add
    another sheet and add a few more ranges. Without some sort of generic
    code the macro would need to be added to each time.

    At the moment I just specify the whole of columns A:X from the first
    row to the last row on the sheet and loop through every cell checking
    the colour and clearing as necessary.

    Which works fine, but takes 20 minutes or so.

    Rgds

    __
    Richard Buttrey
    Grappenhall, Cheshire, UK
    __________________________

  6. #6
    Pete_UK
    Guest

    Re: Deleting contents of cells in non contiguous ranges

    Richard,

    are you saying that every cell in the range B30:X50 has to have its
    contents erased? If that is the case, you only need to check down
    column B looking for coloured cells and build up the start_row and
    end_row from that. Seems pointless in checking every cell in the range
    (unless I'm missing something).

    Pete


  7. #7
    Richard Buttrey
    Guest

    Re: Deleting contents of cells in non contiguous ranges

    On 31 Mar 2006 14:19:13 -0800, "Pete_UK" <pashurst@auditel.net> wrote:

    >Richard,
    >
    >are you saying that every cell in the range B30:X50 has to have its
    >contents erased? If that is the case, you only need to check down
    >column B looking for coloured cells and build up the start_row and
    >end_row from that. Seems pointless in checking every cell in the range
    >(unless I'm missing something).
    >
    >Pete



    Thanks Pete,

    Good idea. I'll give that a go.

    Rgds
    __
    Richard Buttrey
    Grappenhall, Cheshire, UK
    __________________________

  8. #8
    Peter T
    Guest

    Re: Deleting contents of cells in non contiguous ranges

    > Which works fine, but takes 20 minutes or so.

    In your earlier reply to Bob you said

    "The one consistent thing about these ranges is that they always go
    from Col B to Col X and always have the same number of rows - say 20."

    You also said 15-20 sheets, so shouldn't take 20 minutes, even in a very
    slow system.

    15-20 sheets x 20 rows x 200 columns with 20% coloured cells shouldn't take
    more than a few seconds.

    Option Explicit
    Sub Sample()
    Dim r As Long, c As Long, s As String
    Dim x, cnt As Long
    ' 6 Sheets with red in random cells
    Range("a1:GR20").Value = 1
    For r = 1 To 20
    For c = 1 To 200
    x = Int(Rnd * 5) + 1
    If x = 3 Then
    Cells(r, c).Interior.ColorIndex = 3
    Cells(r, c) = 222
    cnt = cnt + 1
    End If
    Next
    Next
    s = ActiveSheet.Name
    For c = 1 To 5
    Worksheets(s).Copy After:=Sheets(Sheets.Count)
    Next
    MsgBox "6 x " & cnt & " red cells"
    End Sub

    Sub test2()
    Dim s As String
    Dim cel As Range, rng As Range
    Dim ws As Worksheet
    'clearcontents of all colorindex-3 cells and remove colour
    For Each ws In ActiveWorkbook.Worksheets
    s = vbNullString
    Set rng = ws.UsedRange
    For Each cel In rng
    If Len(s) > 230 Then
    fnClear s, rng.Parent
    s = vbNullString
    End If

    If cel.Interior.ColorIndex = 3 Then
    s = s & cel.Address(0, 0) & ","
    End If
    Next
    If Len(s) Then
    fnClear s, rng.Parent
    End If
    Next
    End Sub

    Function fnClear(sAddr As String, ws As Worksheet)

    If Right$(sAddr, 1) = (",") Then
    sAddr = Left(sAddr, Len(sAddr) - 1)
    End If

    With ws.Range(sAddr)
    .ClearContents
    .Interior.ColorIndex = xlNone
    End With
    End Function

    If you want to clear all coloured cells change

    If cel.Interior.ColorIndex = 3 Then
    to
    If cel.Interior.ColorIndex > 0 Then

    Regards,
    Peter T



    "Richard Buttrey" <chaos.theory.nospam.removethis@zen.co.uk> wrote in
    message news:mm9r22l8id4p65kf2pc9l8ptvipf544knu@4ax.com...
    > On Fri, 31 Mar 2006 20:55:47 +0100, "Bob Phillips"
    > <bob.phillips@notheretiscali.co.uk> wrote:
    >
    > >If they are always the same cells, surely the colour is irrelevant.

    Couldn't
    > >you just use
    > >
    > >range("B30:X50,B70:X90,B130:X150").ClearContents

    >
    > Bob,
    >
    > Yes I could do that. I was hoping to avoid having to hard code or name
    > these ranges and have some sort of generic code.
    >
    > There are about 15 sheets with an average of 4 ranges on each sheet.
    > The other complication is that from time to time the user needs to add
    > another sheet and add a few more ranges. Without some sort of generic
    > code the macro would need to be added to each time.
    >
    > At the moment I just specify the whole of columns A:X from the first
    > row to the last row on the sheet and loop through every cell checking
    > the colour and clearing as necessary.
    >
    > Which works fine, but takes 20 minutes or so.
    >
    > Rgds
    >
    > __
    > Richard Buttrey
    > Grappenhall, Cheshire, UK
    > __________________________




  9. #9
    Peter T
    Guest

    Re: Deleting contents of cells in non contiguous ranges

    PS, forgot to add if sheets might have a large usedrange, eg much more than
    20 rows you might want to limit the rng, eg

    Set rng = Intersect(ws.UsedRange, ws.Rows("2:30"))

    this eg assumes you never need to check row 1 and below row 30

    Regards,
    Peter T


    "Peter T" <peter_t@discussions> wrote in message
    news:OqlQIpaVGHA.4384@TK2MSFTNGP12.phx.gbl...
    > > Which works fine, but takes 20 minutes or so.

    >
    > In your earlier reply to Bob you said
    >
    > "The one consistent thing about these ranges is that they always go
    > from Col B to Col X and always have the same number of rows - say 20."
    >
    > You also said 15-20 sheets, so shouldn't take 20 minutes, even in a very
    > slow system.
    >
    > 15-20 sheets x 20 rows x 200 columns with 20% coloured cells shouldn't

    take
    > more than a few seconds.
    >
    > Option Explicit
    > Sub Sample()
    > Dim r As Long, c As Long, s As String
    > Dim x, cnt As Long
    > ' 6 Sheets with red in random cells
    > Range("a1:GR20").Value = 1
    > For r = 1 To 20
    > For c = 1 To 200
    > x = Int(Rnd * 5) + 1
    > If x = 3 Then
    > Cells(r, c).Interior.ColorIndex = 3
    > Cells(r, c) = 222
    > cnt = cnt + 1
    > End If
    > Next
    > Next
    > s = ActiveSheet.Name
    > For c = 1 To 5
    > Worksheets(s).Copy After:=Sheets(Sheets.Count)
    > Next
    > MsgBox "6 x " & cnt & " red cells"
    > End Sub
    >
    > Sub test2()
    > Dim s As String
    > Dim cel As Range, rng As Range
    > Dim ws As Worksheet
    > 'clearcontents of all colorindex-3 cells and remove colour
    > For Each ws In ActiveWorkbook.Worksheets
    > s = vbNullString
    > Set rng = ws.UsedRange
    > For Each cel In rng
    > If Len(s) > 230 Then
    > fnClear s, rng.Parent
    > s = vbNullString
    > End If
    >
    > If cel.Interior.ColorIndex = 3 Then
    > s = s & cel.Address(0, 0) & ","
    > End If
    > Next
    > If Len(s) Then
    > fnClear s, rng.Parent
    > End If
    > Next
    > End Sub
    >
    > Function fnClear(sAddr As String, ws As Worksheet)
    >
    > If Right$(sAddr, 1) = (",") Then
    > sAddr = Left(sAddr, Len(sAddr) - 1)
    > End If
    >
    > With ws.Range(sAddr)
    > .ClearContents
    > .Interior.ColorIndex = xlNone
    > End With
    > End Function
    >
    > If you want to clear all coloured cells change
    >
    > If cel.Interior.ColorIndex = 3 Then
    > to
    > If cel.Interior.ColorIndex > 0 Then
    >
    > Regards,
    > Peter T
    >
    >
    >
    > "Richard Buttrey" <chaos.theory.nospam.removethis@zen.co.uk> wrote in
    > message news:mm9r22l8id4p65kf2pc9l8ptvipf544knu@4ax.com...
    > > On Fri, 31 Mar 2006 20:55:47 +0100, "Bob Phillips"
    > > <bob.phillips@notheretiscali.co.uk> wrote:
    > >
    > > >If they are always the same cells, surely the colour is irrelevant.

    > Couldn't
    > > >you just use
    > > >
    > > >range("B30:X50,B70:X90,B130:X150").ClearContents

    > >
    > > Bob,
    > >
    > > Yes I could do that. I was hoping to avoid having to hard code or name
    > > these ranges and have some sort of generic code.
    > >
    > > There are about 15 sheets with an average of 4 ranges on each sheet.
    > > The other complication is that from time to time the user needs to add
    > > another sheet and add a few more ranges. Without some sort of generic
    > > code the macro would need to be added to each time.
    > >
    > > At the moment I just specify the whole of columns A:X from the first
    > > row to the last row on the sheet and loop through every cell checking
    > > the colour and clearing as necessary.
    > >
    > > Which works fine, but takes 20 minutes or so.
    > >
    > > Rgds
    > >
    > > __
    > > Richard Buttrey
    > > Grappenhall, Cheshire, UK
    > > __________________________

    >
    >




  10. #10
    Richard Buttrey
    Guest

    Re: Deleting contents of cells in non contiguous ranges


    Peter,

    Thanks for taking the time to post this.

    I'll give it a whirl during the week and compare it with my current
    routine.

    Regards


    On Sat, 1 Apr 2006 17:48:34 +0100, "Peter T" <peter_t@discussions>
    wrote:

    >PS, forgot to add if sheets might have a large usedrange, eg much more than
    >20 rows you might want to limit the rng, eg
    >
    >Set rng = Intersect(ws.UsedRange, ws.Rows("2:30"))
    >
    >this eg assumes you never need to check row 1 and below row 30
    >
    >Regards,
    >Peter T
    >
    >
    >"Peter T" <peter_t@discussions> wrote in message
    >news:OqlQIpaVGHA.4384@TK2MSFTNGP12.phx.gbl...
    >> > Which works fine, but takes 20 minutes or so.

    >>
    >> In your earlier reply to Bob you said
    >>
    >> "The one consistent thing about these ranges is that they always go
    >> from Col B to Col X and always have the same number of rows - say 20."
    >>
    >> You also said 15-20 sheets, so shouldn't take 20 minutes, even in a very
    >> slow system.
    >>
    >> 15-20 sheets x 20 rows x 200 columns with 20% coloured cells shouldn't

    >take
    >> more than a few seconds.
    >>
    >> Option Explicit
    >> Sub Sample()
    >> Dim r As Long, c As Long, s As String
    >> Dim x, cnt As Long
    >> ' 6 Sheets with red in random cells
    >> Range("a1:GR20").Value = 1
    >> For r = 1 To 20
    >> For c = 1 To 200
    >> x = Int(Rnd * 5) + 1
    >> If x = 3 Then
    >> Cells(r, c).Interior.ColorIndex = 3
    >> Cells(r, c) = 222
    >> cnt = cnt + 1
    >> End If
    >> Next
    >> Next
    >> s = ActiveSheet.Name
    >> For c = 1 To 5
    >> Worksheets(s).Copy After:=Sheets(Sheets.Count)
    >> Next
    >> MsgBox "6 x " & cnt & " red cells"
    >> End Sub
    >>
    >> Sub test2()
    >> Dim s As String
    >> Dim cel As Range, rng As Range
    >> Dim ws As Worksheet
    >> 'clearcontents of all colorindex-3 cells and remove colour
    >> For Each ws In ActiveWorkbook.Worksheets
    >> s = vbNullString
    >> Set rng = ws.UsedRange
    >> For Each cel In rng
    >> If Len(s) > 230 Then
    >> fnClear s, rng.Parent
    >> s = vbNullString
    >> End If
    >>
    >> If cel.Interior.ColorIndex = 3 Then
    >> s = s & cel.Address(0, 0) & ","
    >> End If
    >> Next
    >> If Len(s) Then
    >> fnClear s, rng.Parent
    >> End If
    >> Next
    >> End Sub
    >>
    >> Function fnClear(sAddr As String, ws As Worksheet)
    >>
    >> If Right$(sAddr, 1) = (",") Then
    >> sAddr = Left(sAddr, Len(sAddr) - 1)
    >> End If
    >>
    >> With ws.Range(sAddr)
    >> .ClearContents
    >> .Interior.ColorIndex = xlNone
    >> End With
    >> End Function
    >>
    >> If you want to clear all coloured cells change
    >>
    >> If cel.Interior.ColorIndex = 3 Then
    >> to
    >> If cel.Interior.ColorIndex > 0 Then
    >>
    >> Regards,
    >> Peter T
    >>
    >>
    >>
    >> "Richard Buttrey" <chaos.theory.nospam.removethis@zen.co.uk> wrote in
    >> message news:mm9r22l8id4p65kf2pc9l8ptvipf544knu@4ax.com...
    >> > On Fri, 31 Mar 2006 20:55:47 +0100, "Bob Phillips"
    >> > <bob.phillips@notheretiscali.co.uk> wrote:
    >> >
    >> > >If they are always the same cells, surely the colour is irrelevant.

    >> Couldn't
    >> > >you just use
    >> > >
    >> > >range("B30:X50,B70:X90,B130:X150").ClearContents
    >> >
    >> > Bob,
    >> >
    >> > Yes I could do that. I was hoping to avoid having to hard code or name
    >> > these ranges and have some sort of generic code.
    >> >
    >> > There are about 15 sheets with an average of 4 ranges on each sheet.
    >> > The other complication is that from time to time the user needs to add
    >> > another sheet and add a few more ranges. Without some sort of generic
    >> > code the macro would need to be added to each time.
    >> >
    >> > At the moment I just specify the whole of columns A:X from the first
    >> > row to the last row on the sheet and loop through every cell checking
    >> > the colour and clearing as necessary.
    >> >
    >> > Which works fine, but takes 20 minutes or so.
    >> >
    >> > Rgds
    >> >
    >> > __
    >> > Richard Buttrey
    >> > Grappenhall, Cheshire, UK
    >> > __________________________

    >>
    >>

    >


    __
    Richard Buttrey
    Grappenhall, Cheshire, UK
    __________________________

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1