CODEWITHSUNDEEP
excelvba
Daisy
Daisy

Reputation: 121

Clear all cells from a certain range that starts from the next blank cell in Column A

I am trying to write some VBA in excel that will clear all cells starting from the next empty cell in Column A (data starts from A2). For example, if A5 is blank then I want A5:P300 to all be cleared (as in all Formula and Data gone). And so on... so if A20 is blank then it deletes everything from A20:P300..

How would I go about writing this? I also need it to refer to the active workbook but a specific worksheet called ("Develop").

Thanks for any help provided.

Sub Clear()

Dim x As Worksheet
Dim rng, cell As Range

Set x = ThisWorkbook.Worksheets("R&DCosts(2)")

Set rng = x.Range("A2:A340").Cells(Rows.Count, 1).End(xlUp)

  For Each cell In rng
   If cell.Value = "" Then

      x.Range(cell.Address & ":P350").ClearContents
      End

    End If

  Next cell

 End Sub

Upvotes: 0

Views: 190

Answers (2)

FaneDuru
FaneDuru

Reputation: 42236

Try this code, please:

Sub clearRange_Bis()
  Dim sh As Worksheet, firstEmpt As Long
  Set sh = ThisWorkbook.Worksheets("R&DCosts(2)")
  firstEmpt = sh.Range("A1").End(xlDown).Row + 1
    If firstEmpt > 1000000 Then
        sh.Range("A2:P300").Clear
    Else
        sh.Range("A" & firstEmpt  & ":P300").Clear
    End If
End Sub

Upvotes: 1

Daghan
Daghan

Reputation: 769

A more simple solution

Option Explicit

Sub Clear()

 Dim x As Worksheet
 Dim rng, cell As Range

 Set x = ThisWorkbook.Worksheets("RDCosts(2)") ' you cannot use "&"

 Set rng = x.Range("A2:A340", Cells(Rows.Count, 1).End(xlUp))

 For Each cell In rng
    If cell.Value = "" Then

        x.Range(cell.Address & ":P350").ClearContents
        End

    End If

 Next cell

 End Sub

Upvotes: 1

Related Questions