Solved

VBA coding

Posted on 2013-05-22
3
302 Views
Last Modified: 2013-05-22
I get a "Run Time Error 91 - Object variable or With block variable not set" at Line 24 when I run the below code. What is my mistake? Thanks.

Private Sub DeleteRowsWithSmallAmountAtIssue()
'   Delete rows that have a value of Delete in Column B.
    Dim rng1 As Range
    Dim rng2 As Range
    Dim lngRow As Long
    'Application.ScreenUpdating = False
    If [b2] <> vbNullString Then
        Set rng1 = Range([b2], [b1].End(xlDown))
    Else
        Set rng1 = [b1]
    End If
    For lngRow = rng1.Rows.Count To 2 Step -1
        If (Cells(lngRow, "B") = "Delete") Then
            'Rows(lngRow).EntireRow.Delete
            If rng2 Is Nothing Then
                Set rng2 = Rows(lngRow)
            Else
                Set rng2 = Union(rng2, Rows(lngRow))
            End If
        Else
        '    If Left$(Cells(lngRow, "c"), 2) <> "CV" Then Rows(lngRow).EntireRow.Delete
        End If
    Next
    rng2.EntireRow.Delete
    Application.ScreenUpdating = True
End Sub

Open in new window

0
Comment
Question by:carlosab
[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
3 Comments
 
LVL 19

Accepted Solution

by:
Ken Butters earned 250 total points
ID: 39187658
Your for loop does not Guarantee that rng2 will be assigned a value.

rng2 is only assigned a value with certainty if the following is true:

If (Cells(lngRow, "B") = "Delete") Then

Open in new window

0
 
LVL 85

Assisted Solution

by:Rory Archibald
Rory Archibald earned 250 total points
ID: 39187693
It's a fairly simple fix though:

If Not rng2 Is Nothing Then rng2.EntireRow.Delete

Open in new window

0
 

Author Closing Comment

by:carlosab
ID: 39187866
Thank you both. I've now discovered that the problem is actually that the For sequence was being skipped. I've assigned points here for the correct answer and posted a related question to: http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_28135796.html
0

Featured Post

Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

Question has a verified solution.

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

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
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…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.

734 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