Results 1 to 6 of 6

How to Delete Cell Contents with VBA

This is a discussion on How to Delete Cell Contents with VBA within the Excel Questions forums, part of the Question Forums category; I have the following VBA code Code: Set datRange = Range("D12:D" & Range("D65536").End(xlUp).Row) numRows = datRange.Rows.Count It counts the number ...

  1. #1
    Board Regular
    Join Date
    Mar 2002
    Location
    Montreal, Quebec
    Posts
    194

    Default How to Delete Cell Contents with VBA

    I have the following VBA code
    Code:
    Set datRange = Range("D12:D" & Range("D65536").End(xlUp).Row)
    numRows = datRange.Rows.Count
    It counts the number of cells (rows) in Column D that contains data. Say D12:D20 contain data. What I want to do is then erase the contents (not the entire row) of any cells in Columns E:H from row 21 and down that contain data. The tricky part is that the number of rows in Column D with data will always be changing. Is there a way to add to the code above that will allow me to delete the content of cells in columns adjacent to Col D starting at the point (row) where the cells in Column D are first empty?

    Thanks

  2. #2
    DRJ
    DRJ is offline
    MrExcel MVP DRJ's Avatar
    Join Date
    Feb 2002
    Location
    California
    Posts
    3,856

    Default

    Code:
    LastRow = Range("D65536").End(xlUp).Row + 1
    
    Range("E" & LastRow & ":H65536").ClearContents

  3. #3
    Board Regular
    Join Date
    Mar 2002
    Location
    Montreal, Quebec
    Posts
    194

    Default Re: How to Delete Cell Contents with VBA

    Thanks...

  4. #4
    DRJ
    DRJ is offline
    MrExcel MVP DRJ's Avatar
    Join Date
    Feb 2002
    Location
    California
    Posts
    3,856

    Default

    Your Welcome

    Take Care

  5. #5
    New Member
    Join Date
    Dec 2011
    Location
    Chennai, Tamilnadhu,India
    Posts
    2

    Smile Re: How to Delete Cell Contents with VBA

    Quote Originally Posted by Skippy View Post
    I have the following VBA code
    Code:
    Set datRange = Range("D12:D" & Range("D65536").End(xlUp).Row)
    numRows = datRange.Rows.Count
    It counts the number of cells (rows) in Column D that contains data. Say D12:D20 contain data. What I want to do is then erase the contents (not the entire row) of any cells in Columns E:H from row 21 and down that contain data. The tricky part is that the number of rows in Column D with data will always be changing. Is there a way to add to the code above that will allow me to delete the content of cells in columns adjacent to Col D starting at the point (row) where the cells in Column D are first empty?

    Thanks
    Dear friend,

    please find the below code . this useful of " Delete contains data '. then now am using the code for example only.u will be set ur correct range .

    sub clear ()
    sheet1.cells.range ("D12:D20"). delete true
    end sub

    this code is delete "D12 to D20" contents
    if u want to delete "D" column please use the range ("D")

    i hope it is very useful of ur work

    Regards,
    M.Mohideen Thasthahir

  6. #6
    MrExcel MVP Rick Rothstein's Avatar
    Join Date
    Apr 2011
    Location
    New Jersey, USA
    Posts
    14,777

    Default Re: How to Delete Cell Contents with VBA

    In one line of code...

    Code:
    Range(Cells(Rows.Count, "D").End(xlUp).Offset(1), Cells(Rows.Count, "D")).Resize(, 5).ClearContents

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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


DMCA.com