+ Reply to Thread
Results 1 to 10 of 10

Delete Contents of Cells

  1. #1
    ir26121973
    Guest

    Delete Contents of Cells

    Hi,

    Wonder if someone can help me please.

    I have a spreadsheet with cells that look blank, they have do visible data
    or formula in them however, when I run a macro to try and copy cells into
    them, it fails.

    I have therefore looked at just running a bit of code that within a given
    range where it finds a blank cell it deletes the content.

    Could someone have a look at the code below that I am using to see where i I
    am going wrong because it doesn't seem to work.

    Sub Delete_Cell_Contents()

    Range("A2:I65536").Select
    If Value = "" Then
    Selection.ClearContents
    End If

    End Sub


    Thanks very much

    Chris

  2. #2
    Norman Jones
    Guest

    Re: Delete Contents of Cells

    Hi Chris,

    Post the problematic code.


    ---
    Regards,
    Norman


    "ir26121973" <[email protected]> wrote in message
    news:[email protected]...
    > Hi,
    >
    > Wonder if someone can help me please.
    >
    > I have a spreadsheet with cells that look blank, they have do visible data
    > or formula in them however, when I run a macro to try and copy cells into
    > them, it fails.
    >
    > I have therefore looked at just running a bit of code that within a given
    > range where it finds a blank cell it deletes the content.
    >
    > Could someone have a look at the code below that I am using to see where i
    > I
    > am going wrong because it doesn't seem to work.
    >
    > Sub Delete_Cell_Contents()
    >
    > Range("A2:I65536").Select
    > If Value = "" Then
    > Selection.ClearContents
    > End If
    >
    > End Sub
    >
    >
    > Thanks very much
    >
    > Chris




  3. #3
    ir26121973
    Guest

    Re: Delete Contents of Cells

    Hi Norman, thanks for getting back to me so quickly.

    If you look at the end of my post the code is at the bottom.

    Many thanks

    Chris

    "Norman Jones" wrote:

    > Hi Chris,
    >
    > Post the problematic code.
    >
    >
    > ---
    > Regards,
    > Norman
    >
    >
    > "ir26121973" <[email protected]> wrote in message
    > news:[email protected]...
    > > Hi,
    > >
    > > Wonder if someone can help me please.
    > >
    > > I have a spreadsheet with cells that look blank, they have do visible data
    > > or formula in them however, when I run a macro to try and copy cells into
    > > them, it fails.
    > >
    > > I have therefore looked at just running a bit of code that within a given
    > > range where it finds a blank cell it deletes the content.
    > >
    > > Could someone have a look at the code below that I am using to see where i
    > > I
    > > am going wrong because it doesn't seem to work.
    > >
    > > Sub Delete_Cell_Contents()
    > >
    > > Range("A2:I65536").Select
    > > If Value = "" Then
    > > Selection.ClearContents
    > > End If
    > >
    > > End Sub
    > >
    > >
    > > Thanks very much
    > >
    > > Chris

    >
    >
    >


  4. #4
    Norman Jones
    Guest

    Re: Delete Contents of Cells

    Hi Chris,

    I was referring to the the code mentioned here:

    >> > however, when I run a macro to try and copy cells into
    >> > them, it fails.



    ---
    Regards,
    Norman



    "ir26121973" <[email protected]> wrote in message
    news:[email protected]...
    > Hi Norman, thanks for getting back to me so quickly.
    >
    > If you look at the end of my post the code is at the bottom.
    >
    > Many thanks
    >
    > Chris




  5. #5
    ir26121973
    Guest

    Re: Delete Contents of Cells

    Norman,

    Apologies, this is the code:

    Sub FindCopy()
    Dim cl As Range
    Dim myRange As Range
    Set myRange = ActiveSheet.Range("$A2:A" & Range("$A65536").End(xlUp).Row)
    For Each cl In myRange
    If cl = "" Then
    cl.Offset(-1, 0).EntireRow.Select
    Selection.Copy
    cl.Select
    ActiveSheet.Paste
    End If
    Next cl
    Range("$A$1").Select
    End Sub

    Norman, this code basically looks at a given range, if it comes across blank
    cells in that range, it copies the row from above and pastes it in the blank
    cells.

    Regards

    Chris

    "Norman Jones" wrote:

    > Hi Chris,
    >
    > I was referring to the the code mentioned here:
    >
    > >> > however, when I run a macro to try and copy cells into
    > >> > them, it fails.

    >
    >
    > ---
    > Regards,
    > Norman
    >
    >
    >
    > "ir26121973" <[email protected]> wrote in message
    > news:[email protected]...
    > > Hi Norman, thanks for getting back to me so quickly.
    > >
    > > If you look at the end of my post the code is at the bottom.
    > >
    > > Many thanks
    > >
    > > Chris

    >
    >
    >


  6. #6
    Norman Jones
    Guest

    Re: Delete Contents of Cells

    Hi Chris,

    Perhaps the 'blank' cells contain one or more spaces. Alternatively, perhaps
    these cells contain a formula which returns an empty string.

    Try:

    '=============>>
    Public Sub FindCopy()
    Dim cl As Range
    Dim myRange As Range

    Set myRange = ActiveSheet.Range("A2:A" & _
    Cells(Rows.Count, "A").End(xlUp).Row)
    For Each cl In myRange
    With cl
    If Len(Trim(.Value)) = 0 Then
    .Offset(-1, 0).EntireRow.Copy _
    Destination:=.Item(1)
    End If
    End With
    Next cl

    End Sub
    '<<=============


    ---
    Regards,
    Norman



    "ir26121973" <[email protected]> wrote in message
    news:[email protected]...
    > Norman,
    >
    > Apologies, this is the code:
    >
    > Sub FindCopy()
    > Dim cl As Range
    > Dim myRange As Range
    > Set myRange = ActiveSheet.Range("$A2:A" &
    > Range("$A65536").End(xlUp).Row)
    > For Each cl In myRange
    > If cl = "" Then
    > cl.Offset(-1, 0).EntireRow.Select
    > Selection.Copy
    > cl.Select
    > ActiveSheet.Paste
    > End If
    > Next cl
    > Range("$A$1").Select
    > End Sub
    >
    > Norman, this code basically looks at a given range, if it comes across
    > blank
    > cells in that range, it copies the row from above and pastes it in the
    > blank
    > cells.
    >
    > Regards
    >
    > Chris
    >
    > "Norman Jones" wrote:
    >
    >> Hi Chris,
    >>
    >> I was referring to the the code mentioned here:
    >>
    >> >> > however, when I run a macro to try and copy cells into
    >> >> > them, it fails.

    >>
    >>
    >> ---
    >> Regards,
    >> Norman
    >>
    >>
    >>
    >> "ir26121973" <[email protected]> wrote in message
    >> news:[email protected]...
    >> > Hi Norman, thanks for getting back to me so quickly.
    >> >
    >> > If you look at the end of my post the code is at the bottom.
    >> >
    >> > Many thanks
    >> >
    >> > Chris

    >>
    >>
    >>




  7. #7
    ir26121973
    Guest

    Re: Delete Contents of Cells

    Norman,

    Thanks so very much much, this works a treat.

    Kind regards, have a good day.

    Chris

    "Norman Jones" wrote:

    > Hi Chris,
    >
    > Perhaps the 'blank' cells contain one or more spaces. Alternatively, perhaps
    > these cells contain a formula which returns an empty string.
    >
    > Try:
    >
    > '=============>>
    > Public Sub FindCopy()
    > Dim cl As Range
    > Dim myRange As Range
    >
    > Set myRange = ActiveSheet.Range("A2:A" & _
    > Cells(Rows.Count, "A").End(xlUp).Row)
    > For Each cl In myRange
    > With cl
    > If Len(Trim(.Value)) = 0 Then
    > .Offset(-1, 0).EntireRow.Copy _
    > Destination:=.Item(1)
    > End If
    > End With
    > Next cl
    >
    > End Sub
    > '<<=============
    >
    >
    > ---
    > Regards,
    > Norman
    >
    >
    >
    > "ir26121973" <[email protected]> wrote in message
    > news:[email protected]...
    > > Norman,
    > >
    > > Apologies, this is the code:
    > >
    > > Sub FindCopy()
    > > Dim cl As Range
    > > Dim myRange As Range
    > > Set myRange = ActiveSheet.Range("$A2:A" &
    > > Range("$A65536").End(xlUp).Row)
    > > For Each cl In myRange
    > > If cl = "" Then
    > > cl.Offset(-1, 0).EntireRow.Select
    > > Selection.Copy
    > > cl.Select
    > > ActiveSheet.Paste
    > > End If
    > > Next cl
    > > Range("$A$1").Select
    > > End Sub
    > >
    > > Norman, this code basically looks at a given range, if it comes across
    > > blank
    > > cells in that range, it copies the row from above and pastes it in the
    > > blank
    > > cells.
    > >
    > > Regards
    > >
    > > Chris
    > >
    > > "Norman Jones" wrote:
    > >
    > >> Hi Chris,
    > >>
    > >> I was referring to the the code mentioned here:
    > >>
    > >> >> > however, when I run a macro to try and copy cells into
    > >> >> > them, it fails.
    > >>
    > >>
    > >> ---
    > >> Regards,
    > >> Norman
    > >>
    > >>
    > >>
    > >> "ir26121973" <[email protected]> wrote in message
    > >> news:[email protected]...
    > >> > Hi Norman, thanks for getting back to me so quickly.
    > >> >
    > >> > If you look at the end of my post the code is at the bottom.
    > >> >
    > >> > Many thanks
    > >> >
    > >> > Chris
    > >>
    > >>
    > >>

    >
    >
    >


  8. #8
    ir26121973
    Guest

    Re: Delete Contents of Cells

    Norman,

    You will see from my later post that the macro works great.

    Not knowing whether you are notified by email whether someone replies to you
    I thought I would hang this off one of your earlier posts.

    Like I said the code works great however it is copying some data that I
    don't want to be copied because it is a unique Id.

    Is there anyway please that I could tweek this code and actually use a
    specific range. I'm new to VB so I'm picking up bits and bobs up where I can.

    Many thanks

    Chris

    "Norman Jones" wrote:

    > Hi Chris,
    >
    > Perhaps the 'blank' cells contain one or more spaces. Alternatively, perhaps
    > these cells contain a formula which returns an empty string.
    >
    > Try:
    >
    > '=============>>
    > Public Sub FindCopy()
    > Dim cl As Range
    > Dim myRange As Range
    >
    > Set myRange = ActiveSheet.Range("A2:A" & _
    > Cells(Rows.Count, "A").End(xlUp).Row)
    > For Each cl In myRange
    > With cl
    > If Len(Trim(.Value)) = 0 Then
    > .Offset(-1, 0).EntireRow.Copy _
    > Destination:=.Item(1)
    > End If
    > End With
    > Next cl
    >
    > End Sub
    > '<<=============
    >
    >
    > ---
    > Regards,
    > Norman
    >
    >
    >
    > "ir26121973" <[email protected]> wrote in message
    > news:[email protected]...
    > > Norman,
    > >
    > > Apologies, this is the code:
    > >
    > > Sub FindCopy()
    > > Dim cl As Range
    > > Dim myRange As Range
    > > Set myRange = ActiveSheet.Range("$A2:A" &
    > > Range("$A65536").End(xlUp).Row)
    > > For Each cl In myRange
    > > If cl = "" Then
    > > cl.Offset(-1, 0).EntireRow.Select
    > > Selection.Copy
    > > cl.Select
    > > ActiveSheet.Paste
    > > End If
    > > Next cl
    > > Range("$A$1").Select
    > > End Sub
    > >
    > > Norman, this code basically looks at a given range, if it comes across
    > > blank
    > > cells in that range, it copies the row from above and pastes it in the
    > > blank
    > > cells.
    > >
    > > Regards
    > >
    > > Chris
    > >
    > > "Norman Jones" wrote:
    > >
    > >> Hi Chris,
    > >>
    > >> I was referring to the the code mentioned here:
    > >>
    > >> >> > however, when I run a macro to try and copy cells into
    > >> >> > them, it fails.
    > >>
    > >>
    > >> ---
    > >> Regards,
    > >> Norman
    > >>
    > >>
    > >>
    > >> "ir26121973" <[email protected]> wrote in message
    > >> news:[email protected]...
    > >> > Hi Norman, thanks for getting back to me so quickly.
    > >> >
    > >> > If you look at the end of my post the code is at the bottom.
    > >> >
    > >> > Many thanks
    > >> >
    > >> > Chris
    > >>
    > >>
    > >>

    >
    >
    >


  9. #9
    Norman Jones
    Guest

    Re: Delete Contents of Cells

    Hi Chris,

    > Is there anyway please that I could tweek this code and actually use a
    > specific range. I'm new to VB so I'm picking up bits and bobs up where I
    > can.


    Inedd you can. Simply replace:

    >> Set myRange = ActiveSheet.Range("A2:A" & _
    >> Cells(Rows.Count, "A").End(xlUp).Row)


    For example, if the specific range of interest is A2:A10, then:

    Set myRange = Activesheet.Range("A2:A10")

    ---
    Regards,
    Norman



    "ir26121973" <[email protected]> wrote in message
    news:[email protected]...
    > Norman,
    >
    > You will see from my later post that the macro works great.
    >
    > Not knowing whether you are notified by email whether someone replies to
    > you
    > I thought I would hang this off one of your earlier posts.
    >
    > Like I said the code works great however it is copying some data that I
    > don't want to be copied because it is a unique Id.
    >
    > Is there anyway please that I could tweek this code and actually use a
    > specific range. I'm new to VB so I'm picking up bits and bobs up where I
    > can.
    >
    > Many thanks
    >
    > Chris
    >
    > "Norman Jones" wrote:
    >
    >> Hi Chris,
    >>
    >> Perhaps the 'blank' cells contain one or more spaces. Alternatively,
    >> perhaps
    >> these cells contain a formula which returns an empty string.
    >>
    >> Try:
    >>
    >> '=============>>
    >> Public Sub FindCopy()
    >> Dim cl As Range
    >> Dim myRange As Range
    >>
    >> Set myRange = ActiveSheet.Range("A2:A" & _
    >> Cells(Rows.Count, "A").End(xlUp).Row)
    >> For Each cl In myRange
    >> With cl
    >> If Len(Trim(.Value)) = 0 Then
    >> .Offset(-1, 0).EntireRow.Copy _
    >> Destination:=.Item(1)
    >> End If
    >> End With
    >> Next cl
    >>
    >> End Sub
    >> '<<=============
    >>
    >>
    >> ---
    >> Regards,
    >> Norman
    >>
    >>
    >>
    >> "ir26121973" <[email protected]> wrote in message
    >> news:[email protected]...
    >> > Norman,
    >> >
    >> > Apologies, this is the code:
    >> >
    >> > Sub FindCopy()
    >> > Dim cl As Range
    >> > Dim myRange As Range
    >> > Set myRange = ActiveSheet.Range("$A2:A" &
    >> > Range("$A65536").End(xlUp).Row)
    >> > For Each cl In myRange
    >> > If cl = "" Then
    >> > cl.Offset(-1, 0).EntireRow.Select
    >> > Selection.Copy
    >> > cl.Select
    >> > ActiveSheet.Paste
    >> > End If
    >> > Next cl
    >> > Range("$A$1").Select
    >> > End Sub
    >> >
    >> > Norman, this code basically looks at a given range, if it comes across
    >> > blank
    >> > cells in that range, it copies the row from above and pastes it in the
    >> > blank
    >> > cells.
    >> >
    >> > Regards
    >> >
    >> > Chris
    >> >
    >> > "Norman Jones" wrote:
    >> >
    >> >> Hi Chris,
    >> >>
    >> >> I was referring to the the code mentioned here:
    >> >>
    >> >> >> > however, when I run a macro to try and copy cells into
    >> >> >> > them, it fails.
    >> >>
    >> >>
    >> >> ---
    >> >> Regards,
    >> >> Norman
    >> >>
    >> >>
    >> >>
    >> >> "ir26121973" <[email protected]> wrote in message
    >> >> news:[email protected]...
    >> >> > Hi Norman, thanks for getting back to me so quickly.
    >> >> >
    >> >> > If you look at the end of my post the code is at the bottom.
    >> >> >
    >> >> > Many thanks
    >> >> >
    >> >> > Chris
    >> >>
    >> >>
    >> >>

    >>
    >>
    >>




  10. #10
    ir26121973
    Guest

    Re: Delete Contents of Cells

    Hi Norman,

    This code works great. I'm looking towards restricting the columns. If
    possible out of a range of columns A:I, I only want to copy columns A:G, but
    for it still to go down the page as your original code did.

    Hope this makes sense.

    Regards

    Chris

    "Norman Jones" wrote:

    > Hi Chris,
    >
    > > Is there anyway please that I could tweek this code and actually use a
    > > specific range. I'm new to VB so I'm picking up bits and bobs up where I
    > > can.

    >
    > Inedd you can. Simply replace:
    >
    > >> Set myRange = ActiveSheet.Range("A2:A" & _
    > >> Cells(Rows.Count, "A").End(xlUp).Row)

    >
    > For example, if the specific range of interest is A2:A10, then:
    >
    > Set myRange = Activesheet.Range("A2:A10")
    >
    > ---
    > Regards,
    > Norman
    >
    >
    >
    > "ir26121973" <[email protected]> wrote in message
    > news:[email protected]...
    > > Norman,
    > >
    > > You will see from my later post that the macro works great.
    > >
    > > Not knowing whether you are notified by email whether someone replies to
    > > you
    > > I thought I would hang this off one of your earlier posts.
    > >
    > > Like I said the code works great however it is copying some data that I
    > > don't want to be copied because it is a unique Id.
    > >
    > > Is there anyway please that I could tweek this code and actually use a
    > > specific range. I'm new to VB so I'm picking up bits and bobs up where I
    > > can.
    > >
    > > Many thanks
    > >
    > > Chris
    > >
    > > "Norman Jones" wrote:
    > >
    > >> Hi Chris,
    > >>
    > >> Perhaps the 'blank' cells contain one or more spaces. Alternatively,
    > >> perhaps
    > >> these cells contain a formula which returns an empty string.
    > >>
    > >> Try:
    > >>
    > >> '=============>>
    > >> Public Sub FindCopy()
    > >> Dim cl As Range
    > >> Dim myRange As Range
    > >>
    > >> Set myRange = ActiveSheet.Range("A2:A" & _
    > >> Cells(Rows.Count, "A").End(xlUp).Row)
    > >> For Each cl In myRange
    > >> With cl
    > >> If Len(Trim(.Value)) = 0 Then
    > >> .Offset(-1, 0).EntireRow.Copy _
    > >> Destination:=.Item(1)
    > >> End If
    > >> End With
    > >> Next cl
    > >>
    > >> End Sub
    > >> '<<=============
    > >>
    > >>
    > >> ---
    > >> Regards,
    > >> Norman
    > >>
    > >>
    > >>
    > >> "ir26121973" <[email protected]> wrote in message
    > >> news:[email protected]...
    > >> > Norman,
    > >> >
    > >> > Apologies, this is the code:
    > >> >
    > >> > Sub FindCopy()
    > >> > Dim cl As Range
    > >> > Dim myRange As Range
    > >> > Set myRange = ActiveSheet.Range("$A2:A" &
    > >> > Range("$A65536").End(xlUp).Row)
    > >> > For Each cl In myRange
    > >> > If cl = "" Then
    > >> > cl.Offset(-1, 0).EntireRow.Select
    > >> > Selection.Copy
    > >> > cl.Select
    > >> > ActiveSheet.Paste
    > >> > End If
    > >> > Next cl
    > >> > Range("$A$1").Select
    > >> > End Sub
    > >> >
    > >> > Norman, this code basically looks at a given range, if it comes across
    > >> > blank
    > >> > cells in that range, it copies the row from above and pastes it in the
    > >> > blank
    > >> > cells.
    > >> >
    > >> > Regards
    > >> >
    > >> > Chris
    > >> >
    > >> > "Norman Jones" wrote:
    > >> >
    > >> >> Hi Chris,
    > >> >>
    > >> >> I was referring to the the code mentioned here:
    > >> >>
    > >> >> >> > however, when I run a macro to try and copy cells into
    > >> >> >> > them, it fails.
    > >> >>
    > >> >>
    > >> >> ---
    > >> >> Regards,
    > >> >> Norman
    > >> >>
    > >> >>
    > >> >>
    > >> >> "ir26121973" <[email protected]> wrote in message
    > >> >> news:[email protected]...
    > >> >> > Hi Norman, thanks for getting back to me so quickly.
    > >> >> >
    > >> >> > If you look at the end of my post the code is at the bottom.
    > >> >> >
    > >> >> > Many thanks
    > >> >> >
    > >> >> > Chris
    > >> >>
    > >> >>
    > >> >>
    > >>
    > >>
    > >>

    >
    >
    >


+ 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