You are here:

Excel/Simple Macro Problem

Advertisement


Question
QUESTION: The below macro is placed in a module in Excel 2003. It works great when values are placed in a column but if the column contains formulas below the last value it will not work. To be precise in Column "A" The first 500 cells may have a simple formula like =if(D1=1,A2+A3,"")etc down the column. So the first 10 rows may have values and the following 490 may only have formulas. For some reason this seems to make the ClearV_Sys1 macro not work. It does not like cells with formulas. Why is that?

Sub ClearV_Sys1()
Dim wsSys1 As Worksheet
Set wsSys1 = ThisWorkbook.Sheets("System 1")
If wsSys1.Range("A65536").End(xlUp).Value < 30 Then
wsSys1.Range("B1:E25").ClearContents
End If
End Sub

ANSWER: dp,

With the sheet where you want to run the macro as the active sheet, then
in the name box type  A65536 and hit enter.  This will take you to A65536

Now hit then end key and then hit the Up arrow (do this sequentially).  

it will take you up to the last filled value.  In your description that would be cell A500

That is what  wsSys1.Range("A65536").End(xlup)
does.  So your macro won't clear the contents of B1:E25 until a number less than 30 is in A500

"" does not evaluate as being less than 30.  

You didn't ask how to fix it, but if the cell will have a number or otherwise have the value of "", then you could do this

Sub ClearV_Sys1()
Dim r As Range, r1 As Range, r2 As Range
Dim wsSys1 As Worksheet
Set wsSys1 = ThisWorkbook.Sheets("System 1")
Set r = wsSys1.Range("A1", wsSys1.Cells(wsSys1.Rows.Count, "A").End(xlUp))
On Error Resume Next
 Set r1 = r.SpecialCells(xlFormulas, xlNumbers)
On Error GoTo 0
If Not r1 Is Nothing Then
 Set r2 = r1.Areas(r1.Areas.Count)
 Set r2 = r2(r2.Count)
 If r2.Value < 30 Then
    wsSys1.Range("B1:E25").ClearContents
 End If
End If

End Sub

--
Regards,
Tom Ogilvy


---------- FOLLOW-UP ----------

QUESTION: Hi Tom,

Would you mind commenting your code. I don't understand what you did exactly.

I am still enjoying how wonderfully it works!

dp

Answer
dp

If you choose column A and do f5 to get the goto dialog, then choose special, then in the resulting dialog, you have a choice of constants or formulas.  Choose formulas, then uncheck every subtype except numbers.  Then click OK.  This should select all the cells where the formula produce a number (the non "" cells)

that is what this line does
Set r1 = r.SpecialCells(xlFormulas, xlNumbers)

the range reference "r" has already been set to a range extending from A1 to the last filled cell in column A using this code:
Set r = wsSys1.Range("A1", wsSys1.Cells(wsSys1.Rows.Count, "A").End(xlUp))

the results would be a reference to the number cells in column A.  However, if there are no number cells, that command would return an error.  So we place it inside an error handler that says to ignore any errors from that command - continue to process the code (On Error Resume Next).  If an error occurs, then r1 would not refer to anything.  It would have a value of "Nothing"  (not the string nothing - the concept of an uninitialized objct).

Since I know nothing of what you are doing, I don't assume the result would be a single contiguous range.  So the r1 could refer to multiple ranges (clumps of multiple or single cells).  That is what the Areas is all about.

r1(r1.areas.count)  will refer to the last Area or clump of contiguous cells in the r1 range.

Now I need the last cell in that Area to get the last cell filled with a number.  


If there are no

Sub ClearV_Sys1()
Dim r As Range, r1 As Range, r2 As Range
Dim wsSys1 As Worksheet
Set wsSys1 = ThisWorkbook.Sheets("System 1")
' set a reference to all the filled cells in column A beginning in A1
Set r = wsSys1.Range("A1", wsSys1.Cells(wsSys1.Rows.Count, "A").End(xlUp))
On Error Resume Next
  ' cull that range down to those cell with formulas that are returning number values
 Set r1 = r.SpecialCells(xlFormulas, xlNumbers)
On Error GoTo 0
If Not r1 Is Nothing Then
 ' now we know that r1 references at least one cell so find the last area in the reference
 Set r2 = r1.Areas(r1.Areas.Count)
 ' find the last cell in the last area
 Set r2 = r2(r2.Count)
 ' now we have the cell we need to check the value of.
 If r2.Value < 30 Then
    wsSys1.Range("B1:E25").ClearContents
 End If
End If

End Sub

--
Regards,
Tom Ogilvy

About Excel
This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here

Excel

All Answers


Answers by Expert:


Ask Experts

Volunteer


Tom Ogilvy

Expertise

Selected as an Excel MVP by Microsoft since 1999. Answering Excel questions in Allexperts since its inception in 2001. Able to answer questions on almost all aspects of Excel's internal capabilities. If seeking a VBA solution, please specify that in your question itself so I give you the answer you want. [Excel has weak protection - if you are distributing an application, I don't answer questions on how to protect your project from your users.]

Experience

Extensive experience.

Education/Credentials
Master of Science (MS) degree Operations Research (ORSA)

Awards and Honors
Microsoft MVP in Excel.

©2016 About.com. All rights reserved.