Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 327
  • Last Modified:

Excel VBA - Modification

I have a button that clears out rows on a tab named 'Retrieval'.  It clears out entire row.   However now I need it to noly goto column AH and not clear anything past that as there are formulas in the columns after that.  I think this is the code.  Not sure how to make it stop at AH??

Sub clearRetrievalData(Optional bPrompt As Boolean = True)
Dim wksRetrieval As Worksheet
Dim lastRow As Long
Dim xMsg As Long

    If bPrompt Then
        xMsg = MsgBox("This will clear ALL data on the Retrieval Tab - Continue?", vbYesNo, "Press YES to continue NO to abort clear command")
        If xMsg = vbNo Then Exit Sub
    End If
   
    Set wksRetrieval = ThisWorkbook.Worksheets("Retrieval")
    'clear Retrieval tab of any data
    lastRow = wksRetrieval.Cells.Find(what:="*", LookIn:=xlValues, lookat:=xlPart, searchorder:=xlByRows, searchdirection:=xlPrevious).Row
   
    'clear move/keep switch, data, and color back to normal
    If lastRow >= 15 Then
        With wksRetrieval.Range("15:15", wksRetrieval.Cells(lastRow, 1).EntireRow)
            .ClearContents
            .Interior.Color = -4142
        End With
    End If
End Sub
0
elwayisgod
Asked:
elwayisgod
1 Solution
 
nutschCommented:
Try this update, adding an Intersect to your range definition:

intersect(wksRetrieval.Range("15:15", wksRetrieval.Cells(lastRow, 1).EntireRow),wksRetrieval.columns("A:AH"))


Sub clearRetrievalData(Optional bPrompt As Boolean = True)
Dim wksRetrieval As Worksheet
Dim lastRow As Long
Dim xMsg As Long

    If bPrompt Then
        xMsg = MsgBox("This will clear ALL data on the Retrieval Tab - Continue?", vbYesNo, "Press YES to continue NO to abort clear command")
        If xMsg = vbNo Then Exit Sub
    End If
   
    Set wksRetrieval = ThisWorkbook.Worksheets("Retrieval")
    'clear Retrieval tab of any data
    lastRow = wksRetrieval.Cells.Find(what:="*", LookIn:=xlValues, lookat:=xlPart, searchorder:=xlByRows, searchdirection:=xlPrevious).Row
   
    'clear move/keep switch, data, and color back to normal
    If lastRow >= 15 Then
        With intersect(wksRetrieval.Range("15:15", wksRetrieval.Cells(lastRow, 1).EntireRow),wksRetrieval.columns("A:AH"))
            .ClearContents
            .Interior.Color = -4142
        End With
    End If
End Sub

Open in new window

0
 
elwayisgodAuthor Commented:
Perfect.  Thanks.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now