Results 1 to 9 of 9

Thread: [RESOLVED] Clear contents in column range based on criteria

  1. #1

    Thread Starter
    New Member
    Join Date
    Feb 2024
    Posts
    5

    Resolved [RESOLVED] Clear contents in column range based on criteria

    Hi,

    I have a workbook where having multiple worksheets and expecting the support to clear the last cell column values for "Comments" and immediate previous column from "Comments" header from entire workbook from row 7. I have provided the google drive link for workbook

    https://drive.google.com/file/d/1Kux...ew?usp=sharing

    The header "Comments" is not fixed with a column in entire workbook. In Sheet1 it is under column J where i expect to clear the values from I7:J11 but in the Sheet2 the header "Comments" located under column P where i expect to clear the values from O7:P11. Likewise the script expect to clear from entire workbook where header "Comments" start with and immediate previous column.

    But the only one thing is common that header "Comments" located at row 6 in entire workbook. Please help me on the same. Thanks a ton in advance.
    Attached Images Attached Images  

  2. #2
    PowerPoster jdc2000's Avatar
    Join Date
    Oct 2001
    Location
    Idaho Falls, Idaho USA
    Posts
    2,376

    Re: Clear contents in column range based on criteria

    It looks like your header is on Row 5. Is there any reason for this, rather than having it on Row 1? Will the header row vary, or will it always be on Row 5?

  3. #3

    Thread Starter
    New Member
    Join Date
    Feb 2024
    Posts
    5

    Re: Clear contents in column range based on criteria

    Quote Originally Posted by jdc2000 View Post
    It looks like your header is on Row 5. Is there any reason for this, rather than having it on Row 1? Will the header row vary, or will it always be on Row 5?
    Hi, Yes there is a reason for this to start with row 5 and it will always remain with Row 5 whereas the "Comments" header will always be at row 6 for entire workbook.

    Hope it clarifies the ask. Thank you

  4. #4
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    4,314

    Re: Clear contents in column range based on criteria

    And your Problem is what?
    Just run through each sheet in its Row 6 looking for "Comment" as a Value, and you have your Column-Index

    BTW: I do hope, you don't have two (or more) columns named "Comment" in each sheet......

    As food for thought: What about named Ranges?
    Last edited by Zvoni; Tomorrow at 31:69 PM.
    ----------------------------------------------------------------------------------------

    One System to rule them all, One Code to find them,
    One IDE to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    Code is like a joke: If you have to explain it, it's bad

  5. #5

    Thread Starter
    New Member
    Join Date
    Feb 2024
    Posts
    5

    Re: Clear contents in column range based on criteria

    Yes exactly, We do not have more than a column named "Comment" as header in each worksheet at row 6 and i expect to clear the contents from row 7 under "Comment" header and immediate previous column before "Comment" as header column.

  6. #6
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    4,314

    Re: Clear contents in column range based on criteria

    I still don't understand your Problem.

    Run in each sheet through Row 6, and look for a Value "Comment"
    If you find it, you can read its Column-index

    Sample-Code
    Code:
    Dim i As Long
    Dim j As Long
    Dim ws As Worksheet
        For j = 1 To ThisWorkbook.Worksheets.Count
            Set ws = ThisWorkbook.Worksheets(j)
            For i = 1 To ws.Cells(6, ws.Columns.Count).End(xlToLeft).Column
                If ws.Cells(6, i) = "Comment" Then
                    ws.Range(ws.Cells(7, i - 1), ws.Cells(11, i)).ClearContents
                    Exit For
                End If
            Next
        Next
    Last edited by Zvoni; Tomorrow at 31:69 PM.
    ----------------------------------------------------------------------------------------

    One System to rule them all, One Code to find them,
    One IDE to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    Code is like a joke: If you have to explain it, it's bad

  7. #7

    Thread Starter
    New Member
    Join Date
    Feb 2024
    Posts
    5

    Re: Clear contents in column range based on criteria

    Thank you Zvoni, its working but the only challenge is not clearing the borders under "Comments" header for each worksheet and only clearing the values. Can you please add the script to clear the border lines as well with cell values. Thank you once again.
    Last edited by rahulmalhotra831103; Feb 13th, 2024 at 11:07 AM.

  8. #8
    PowerPoster jdc2000's Avatar
    Join Date
    Oct 2001
    Location
    Idaho Falls, Idaho USA
    Posts
    2,376

    Re: Clear contents in column range based on criteria

    Code:
    Option Explicit
    
    
    Sub ClearComments()
    
        Dim lngRow1 As Long
        Dim intCommentsColumn As Integer
        Dim intTemp1 As Integer, intTemp2 As Integer
        Dim lngTemp1 As Long
        Dim strTemp1 As String
        
        lngTemp1 = ActiveSheet.Cells.Rows.Count
        
        Application.ScreenUpdating = False
        
        intTemp2 = 0
        intCommentsColumn = 0
        ' Find Comments header (always in Row 6)
        For intTemp1 = 26 To 1 Step -1
            If intCommentsColumn = 0 And intTemp2 = 0 Then
                strTemp1 = UCase$(CStr(ActiveSheet.Cells(6, intTemp1)))
                If strTemp1 = "COMMENTS" Then
                    intCommentsColumn = intTemp1
                Else
                    If ActiveSheet.Cells(5, intTemp1) <> "" Then
                        intTemp2 = -1
                    End If
                End If
            End If
        Next intTemp1
        If intCommentsColumn = 0 Then
            MsgBox "Comments column not found!", vbOKOnly, "Error"
            Exit Sub
        End If
        
        ' Clear the Comments column data
        For lngRow1 = 7 To lngTemp1
            ActiveSheet.Cells(lngRow1, intCommentsColumn) = ""
            Application.StatusBar = "Comments cleared, Row " & CStr(lngRow1)
            DoEvents
        Next lngRow1
        
        Application.ScreenUpdating = True
        Application.StatusBar = "Ready"
        
    End Sub

  9. #9

    Thread Starter
    New Member
    Join Date
    Feb 2024
    Posts
    5

    Re: Clear contents in column range based on criteria

    Thanks a ton Zvoni and jdc2000. Provided script working perfectly now. Thanks a ton

Posting Permissions

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



Click Here to Expand Forum to Full Width