Code Adjustment to clear contents from a range instead of deleting entire rows needed

kelly mort

Well-known Member
Joined
Apr 10, 2017
Messages
1,796
Office Version
  1. 2016
Platform
  1. Windows
This code deletes the entire row. Now I want to clear just data from a given range intead. I want to clear data from T4:X & LastusedRow. V contains the dates. I have been trying to fix it but seems hard to read and understand it. Actually the dates are sorted in descending order.

Code:
Sub DeleteRows()
    Dim c As Range, DeleteRange As Range, DataRange As Range
    Dim LR As Long
    
'change sheet name as required
    With Worksheets("[COLOR=#ff0000]Sheet1[/COLOR]")
'find last row in range
        LR = .Cells(.Rows.Count, "B").End(xlUp).Row
'range you are searching
        Set DataRange = .Range("B1:B" & LR)
    End With


    DataRange.EntireRow.Hidden = False


    For Each c In DataRange.Cells
        If IsDate(c.Value) Then
            If Date - DateValue(c.Value) > 15 Then
                If DeleteRange Is Nothing Then
                    Set DeleteRange = c
                Else
                    Set DeleteRange = Union(DeleteRange, c)
                End If
            End If
        End If
    Next c
'delete all matched rows in one go
    If Not DeleteRange Is Nothing Then DeleteRange.EntireRow.Delete
End Sub
 

Some videos you may like

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.

Akuini

Well-known Member
Joined
Feb 1, 2016
Messages
2,575
Office Version
  1. 365
Platform
  1. Windows
Try changing this:
Code:
DeleteRange.EntireRow.Delete

to this:
Code:
DeleteRange.ClearContents
 

kelly mort

Well-known Member
Joined
Apr 10, 2017
Messages
1,796
Office Version
  1. 2016
Platform
  1. Windows
Try changing this:
Code:
DeleteRange.EntireRow.Delete

to this:
Code:
DeleteRange.ClearContents

I tried this.

It deleted only from column V not from T to X


Okay this worked:

Code:
DeleteRange.Offset(0, -2).Resize(, 5).ClearContents


Thanks
 
Last edited:

Akuini

Well-known Member
Joined
Feb 1, 2016
Messages
2,575
Office Version
  1. 365
Platform
  1. Windows
You're welcome, glad you figured it out.
 

Watch MrExcel Video

Forum statistics

Threads
1,108,816
Messages
5,525,064
Members
409,617
Latest member
Lenaf

This Week's Hot Topics

Top