June 19, 2012

select the non empty cell and clear the content to allow another selection in Excel

select the non empty cell and clear the content to allow another selection.

 Sub ReferenceNonEmptyCells()  
   Dim rngAll As Range  
   Dim rngConstants As Range  
   Dim rngFormulas As Range  
   ' 1. reference complete range  
   Set rngAll = Worksheets("Data").Range("A1:K30&qu ot;)  
   ' or use named range...  
   Set rngAll = Worksheets("Data").Range("MyRange&q uot;)  
   ' Reference Constants (23=Numbers+Text+Logical+Errors)  
   On Error Resume Next  
   Set rngConstants = rngAll.SpecialCells(xlCellTypeConstants, 23)  
   ' Reference Formulas  
   Set rngFormulas = rngAll.SpecialCells(xlCellTypeFormulas, 23)  
   '--  
 ' Clear (or ClearContents) Non Empty Cells  
   If Not rngConstants Is Nothing Then  
     rngConstants.ClearContents  
   End If  
   If Not rngFormulas Is Nothing Then  
     rngFormulas.ClearContents  
   End If  
 End Sub