0

I want to run the code that will clear the rows in specific ranges of my sheet. I have also the condition which rows' content should be cleared i.e. if the ID in the first column of my range matches the defined name with the first characters (i.e. if the ID in the column has more characters than a defined name but it matches with the first characters - the row content should be erased )

I would like to that for a few ranges but for now, I am trying on one range as it doesn't come as it should.

Here is how the case looks before running the code: enter image description here

This my DESIRED outcome => the rows in the array where ID matches to defined name clear in the range: enter image description here

My code does not repsond at all. No error message, nothing and within that no outcome I expect:

Option Explicit
Sub EraseArray()
Dim r As Long
Dim endRow As Long
Dim StartRow As Long
Dim TargetSheet As Worksheet

Const ColumnStart1 As Long = 2
Const ColumnEnd1 As Long = 5

Const ColumnStart2 As Long = 7   'to add
Const ColumnEnd2 As Long = 10    ' to add

Const ColumnStart3 As Long = 12   'to add
Const ColumnEnd3 As Long = 15     'to add


Const l_MyDefinedName As String = "ID"
Dim ColumnNo As Integer
Dim ClearRange As Range



Set TargetSheet = ThisWorkbook.Sheets("Sheet1")


With TargetSheet

StartRow = 8
Dim lngLastRow As Long
        lngLastRow = .Cells(.Rows.Count, ColumnStart1).End(xlUp).Row   '
        Set ClearRange = .Range(.Cells(StartRow, ColumnStart1), .Cells(lngLastRow, ColumnEnd1))

Dim ID As String

      ID = ThisWorkbook.Names(l_MyDefinedName).RefersToRange.Value


    With ClearRange
        Dim MatchID As String


        For StartRow = 15 To ClearRange.Rows.Count

             MatchID = Left(.Cells(StartRow, ColumnStart1), ColumnStart1)

            If MatchID = ID Then

            For ColumnNo = ColumnStart1 To ColumnEnd1
'

                 '*********Clear what is inside********'

        TargetSheet.Cells(StartRow, ColumnNo).ClearContent



            Next ColumnNo

        StartRow = StartRow + 1

            End If
Next StartRow
End With
End With


End Sub

Anybody who could help on that?

Improve this question
6
  • Have you checked that the sub is even running? One way to check is to add the line MsgBox "Sub EraseArray was called!" inside, but near the top of, the sub (preferably before any Set's, With's, or anything other than Const's and Dim's). Commented May 7, 2018 at 19:07
  • Do you mean to delete the row or clear the contents? Much better question btw though you should clarify what you mean by not responsive. Commented May 7, 2018 at 19:09
  • Looks like you are operating on 2 different ranges. Range 1 B:E and Range 2: G:J. And that for range 1 if Col B has 1234 at start then clear contents in the B:E range ; similarly for Col G clear in G:J range. Commented May 7, 2018 at 19:14
  • Why For StartRow = 15 if above it was StartRow = 8? Also, in your example ClearRange.Rows.Count = 7, so you're looping from 15 to 7, and it doesn't look like you meant to Step -1. Commented May 7, 2018 at 19:17
  • Are IDs always numeric and of length 4? Commented May 7, 2018 at 19:20

1 Answer 1

Reset to default
1

tl;dr

Here is a version. I have deliberately stuck with your idea of using constants to set up individual ranges to work with, setting up startRow and the like to help you see how your script could evolve into what is shown below.

You are working with 3 distinct ranges whose boundaries you set with constants. I put those into an array which is looped. I access the items in start column and end column pairs, with Index and Index + 1, to set up each clear range. I use step 2 in the loop so pairs don't overlap.

I then test the first column of that range for the presence of the ID. If present, I gather that in to an union'd range which I resize to the number of columns in the clear range, e.g. If a row in column B has 1234 in it, I would resize the cell in that row in column B to B:E in that row and add it to the union'd range. Bit like putting ranges into a basket to hold them to deal with later.

At the end I test if unionRng, the union'd ranges, is not nothing i.e. if the basket has something in it; meaning matches found, and then clear the contents of those cells.

Note:

  1. It requires you to have a named range called "ID", and that that corresponds with D3 in sheet1.
  2. I have changed the notation to match with norms for Constants which is all upper case. I am not a fan of using "_" in variables/constants. I have put here for the sake of legibility. Maybe consider different names.

Code:

Option Explicit 
Public Sub ClearCells()

    Const COLUMN_START1 As Long = 2
    Const COLUMN_END1 As Long = 5
    Const COLUMN_START2 As Long = 7
    Const COLUMN_END2 As Long = 10
    Const COLUMN_START3 As Long = 12
    Const COLUMN_END3 As Long = 15
    Const START_ROW As Long = 8
    Const L_MY_DEFINED_NAME As String = "ID"

    Dim loopRanges()

    loopRanges = Array(COLUMN_START1, COLUMN_END1, COLUMN_START2, COLUMN_END2, COLUMN_START3, COLUMN_END3)

    Dim targetSheet As Worksheet, index As Long, unionRng As Range, 
    Dim id As Long                               'Or , ID As String?

    Set targetSheet = ThisWorkbook.Sheets("Sheet1")
    id = ThisWorkbook.Names(L_MY_DEFINED_NAME).RefersToRange.Value

    Application.ScreenUpdating = False

    With targetSheet

        For index = LBound(loopRanges) To UBound(loopRanges) Step 2

            Dim lngLastRow As Long, ClearRange As Range,rng As Range

            lngLastRow = .Cells(.Rows.Count, loopRanges(index)).End(xlUp).Row '
            If lngLastRow < START_ROW Then lngLastRow = START_ROW

            Set ClearRange = .Range(.Cells(START_ROW, loopRanges(index)), .Cells(lngLastRow, loopRanges(index + 1)))

            For Each rng In ClearRange.Columns(1).Cells
                If Not IsEmpty(rng) Then
                    If Left$(rng.Value, Len(id)) = id Then '<== match found
                        If Not unionRng Is Nothing Then
                            Set unionRng = Union(unionRng, rng.Resize(1, ClearRange.Columns.Count)) '<== gather all matches into a union range
                        Else
                            Set unionRng = rng.Resize(1, ClearRange.Columns.Count)
                        End If
                    End If
                End If
            Next rng
        Next index
    End With

    If Not unionRng Is Nothing Then Debug.Print unionRng.Address '<== or unionRng.ClearContents
    Application.ScreenUpdating = True
End Sub
Improve this answer
Sign up to request clarification or add additional context in comments.

5 Comments

Woow! I am so amazed. The code is great! In the line before the last, I ultimately chose 'unionRng.ClearContents' - and it runs perfectly!. I am sorry for remaining unclarities in my question but your answer covers beyond :) I have never worked (and tried to avoid) Upper&lower bounds but now I see, I need to have a better look at that. Thank you !
I left it as printing the address with the delete commented out as it is good practice to check what you are clearing before clearing/deleting. Any further questions let me know.
Let me know if any further clarification required. It is good practice to use Union to collect ranges together and then perform an operation,like delete, once with the union'd range. It is more efficient.
I will definitely practice that and keep you in mind for any question. Thank you for that.
p.s. Union will only work for ranges in the same sheet, not across sheets.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.