How to Delete Cell Contents with VBA

[Skippy]

I have the following VBA 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

 

[DRJ]

LastRow = Range("D65536").End(xlUp).Row + 1

Range("E" & LastRow & ":H65536").ClearContents

 

[Skippy]

Thanks...

 

[DRJ]

Your Welcome

Take Care

 

[mohideen thasthair]

I have the following VBA 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

 

[Rick Rothstein]

In one line of code...

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