Solved

Excel VBA - Modification

Posted on 2013-02-06
2
323 Views
Last Modified: 2013-02-06
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
Comment
Question by:elwayisgod
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 39

Accepted Solution

by:
nutsch earned 500 total points
ID: 38860412
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
 

Author Closing Comment

by:elwayisgod
ID: 38861630
Perfect.  Thanks.
0

Featured Post

Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

687 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question