p2p.wrox.com Forums

Need to download code?

View our list of code downloads.


  Return to Index  

vba_excel thread: if lenth of a cell is 0 delete contents


Message #1 by "mike kerr" <mikezcg@y...> on Mon, 28 Jan 2002 15:27:18
I like to work from the bottom up when doing these things because if you
delete instead of clear the contents it makes code much simpler.  If this is
the only reason you are using column K then you really don't need it.

Sub TryAgain()
   Dim lngRows As Long

   lngRows = Range("J1").CurrentRegion.Rows.Count
   For lngRows = lngRows To 2 Step -1
      If Len(Cells(lngRows, 10)) = 0 Then
         'uncomment this and comment out the next line
         'if you want to delete the rows instead of
         'just clearing them
         'Rows(lngRows).Delete
         Range(Cells(lngRows, 10), Cells(lngRows, 11)).ClearContents
      End If
   Next
End Sub

>-----Original Message-----
>From: mike kerr [mailto:mikezcg@y...]
>Sent: Monday, January 28, 2002 3:27 PM
>To: VBA_Excel
>Subject: [vba_excel] if lenth of a cell is 0 delete contents
>
>
>Column J         Column K
>Social Sec #     =len(j*)
>
>
>I need the above structure (dont ask why!)
>
>I am trying to eval if this is a zero(Column k) if so i want to delete the
>contents of the same row in Col J. then return to row k and move down one
>row, until i reach row 4483.  Now i know i dont even need to select this
>cell to delete the contents and My object model skills are nills so i
>tried:
>
>   Sub tryagian()
>
>   Dim eval As Integer
>   ActiveCell.Select
>
>Do Until ActiveCell.Range("K4483")
>    ActiveCell.Select
>    eval = CInt(ActiveCell.Value)
>    If eval = 0 Then
>        ActiveCell.Offset(0, -1).Range("A1").Select
>        Selection.ClearContents
>    End If
>        ActiveCell.Offset(1, 1).Range("A1").Select
>Loop
>
>End Sub
>
>
>ok ok stop laughing and lend a brain cell (i havent turned mine on yet
>today.)
>
>Thanks
>
>
>mike
>
>


  Return to Index