asked on

Problem with a VB Macro

Hi there,
I have written this code (i have very basic knowledge of VB) to remove any items less than '100'
and it does not seem to remove anything.
have I written this completley wrong?
Please help

Sub go()
'
' go Macro
'
'
    Sheets("Sheet1").Select
    Cells.Select
    Selection.Copy
    Sheets("Sheet2").Select
    Application.Run "BLPLinkReset"
    Range("A1").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("A1").Select
    
      Dim i As Integer
 
  Range("C3").Select
 
    For i = 1 To 200
 
     If ActiveCell.Value > "100" Then
    
      Selection.Offset(1, 0).Select
  
    Else
    
    Selection.EntireRow.Delete
    
    End If
    
    Next i
 
    
    
End Sub

Open in new window

jppinto

Sub remove_rows()
    Dim sh As Worksheet
    Set sh = ActiveSheet
    Dim cll As Range
    For Each sh In ActiveWorkbook.Sheets
        For Each cll In sh.UsedRange
           If cll.Value=100 Then
              cll.Select
              Selection.Delete Shift:=xlUp
           End If
        Next cll
    Next sh
End Sub

Open in new window

ASKER CERTIFIED SOLUTION

byundt

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

jppinto

Did you at least tryed my code?!?!

byundt

jppinto,
I looked at your code before posting, and noticed that:
1) It applied to every cell in the worksheet, not just C3:C203 as in Asker's original code. I realize that the question body implied deleting just the cell values, but felt the steps taken in the Asker's code were more illustrative of the Asker's intent.
2) It deleted only cell values that equalled 100, where the original code looked for values that were less than or equal 100. The question body confused matters somewhat by calling for deleting values less than 100.
3) It deleted just the cells, where the Asker's macro deleted the entire row
4) If you have a series of cells in a column that all have a value of 100, only the first, third, fifth, etc. will get deleted. This is because you didn't adjust the selection after deleting a cell. The preferred approach when deleting rows is to work from the bottom up, thereby avoiding the need to adjust the selection or loop index.

Brad

jppinto

Hello Brad,

No problem, but at least YOU gave me some feedback while the Author didn't say if my code was OK for what he wanted...

Regards,

jppinto

garethmanuel

ASKER

A colleague of mine used my account to post this question while i was out of the office. He is new to EE and did not understand the etiquette used on this site. Agreed on Brad's comments. Thanks for both submissions.

jppinto

OK, no problem...

jppinto

byundt

João,
One of the benefits of trying to answer Experts-Exchange questions is improving your knowledge. I figure you will never again forget about looping from the bottom up when deleting cells or rows, so at least two people have been helped in this thread.

Putting on my Zone Advisor hat, I'm glad to see that you are participating in as many Excel questions as you are. Because many long-term experts reduce their site participation, we need to develop new experts on a continuing basis. Your contributions have been valuable and I hope that you continue to enjoy participating in EE threads in the future.

Brad

jppinto

Hello Brad,

Yes, it's true what you just said. When I started in EE, my primary target was win points to get up in ranks. With time I develop a passion to help others, as others had help me in the past here in EE. Helping others has made me try new things and learn a lot, specially with Excel. With the experience I have in EE I decided, one week ago, to start a "blog" where I can put some of the things that I've been learning here in the way that I can pass to others that knowledge. The link is below. Last month I spent a lot of my time here in EE, answered more that 100 questions and made 167,535 points. I'm proud in what I achieve and happy to see that I could help so many people. I will continue to be an active member of EE, at least whenever I have the time for this. Thanks a lot for your encouraging words.

Best regards,

João

http://excel-user.blogspot.com/