Results 1 to 11 of 11
  1. #1
    3 Star Lounger
    Join Date
    Nov 2001
    Posts
    342
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hi, after doing a range/value command, I want to remove rows where #DIV/0! appears in column V. I have tried the following but I get a mismatch error:

    'Get rid of #DIV/0! errors
    Dim lngRow As Long, lngMaxRow As Long, msgin
    lngMaxRow = Range("v65536").End(xlUp).Row
    msgin = "#DIV/0!"
    For lngRow = lngMaxRow To 4 Step -1
    If Cells(lngRow, 22) = msgin Then
    Cells(lngRow).EntireRow.Delete
    End If
    Next lngRow


    Can anyone help to identify what I've done wrong?

    Thanks in advance.

  2. #2
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts
    See if this helps:

    Code:
    Sub RemoveErrorLines()
    	Dim oErrorRange As Range
    	Set oErrorRange = ThisWorkbook.Worksheets("Sheet1").UsedRange
    	'Select just the error cells in column A
    	Set oErrorRange = oErrorRange.Range("A:A").SpecialCells(xlCellTypeFormulas, xlErrors)
    	oErrorRange.EntireRow.Delete
    End Sub
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  3. #3
    3 Star Lounger
    Join Date
    Nov 2001
    Posts
    342
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thanks Jan,

    In order to modify your code for my situation, I've created the following:


    'First of all, determine the range, how far down the formulas go
    Dim lngRow As Long, lngMaxRow As Long, msgin, oErrorRange As Range
    lngMaxRow = Range("v65536").End(xlUp).Row
    msgin = "v1:v" & lngMaxRow

    Set oErrorRange = ThisWorkbook.Worksheets("Quotes").UsedRange 'Quotes is the name of the sheet.
    'Select just the error cells in column V
    Set oErrorRange = oErrorRange.Range(msgin).SpecialCells(xlCellTypeFo rmulas, xlErrors) 'msgin contains the cells I want to test
    oErrorRange.EntireRow.Delete


    but I get an error message saying 'no cells found' at the last 'Set oErrorRange' line.

    What am I doing wrong here? Thanks again.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Try replacing the lines

    Set oErrorRange = ThisWorkbook.Worksheets("Quotes").UsedRange 'Quotes is the name of the sheet.
    'Select just the error cells in column V
    Set oErrorRange = oErrorRange.Range(msgin).SpecialCells(xlCellTypeFo rmulas, xlErrors) 'msgin contains the cells I want to test

    with

    'Select just the error cells in column V
    Set oErrorRange = Range(msgin).SpecialCells(xlCellTypeFormulas, xlErrors) 'msgin contains the cells I want to test

  5. #5
    3 Star Lounger
    Join Date
    Nov 2001
    Posts
    342
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thanks Hans, but I still get the same 'no cells found' error. When I hover the mouse over the variables, they read:

    msgin = v1:v793 'correct
    xlCellTypeformulas = -4123 'is this correct?
    xlerrors = 16 'actually there are 18


    The code now reads:

    'Now get rid of #DIV/0! errors
    Dim lngRow As Long, lngMaxRow As Long, msgin, oErrorRange As Range
    lngMaxRow = Range("v65536").End(xlUp).Row
    msgin = "v1:v" & lngMaxRow

    'Select just the error cells in column V
    Set oErrorRange = Range(msgin).SpecialCells(xlCellTypeFormulas, xlErrors) 'msgin contains the cells I want to test
    oErrorRange.EntireRow.Delete

    Thanks again for any further help.

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    The error message occurs if there are no cells with errors within the specified range. To avoid this, add an error handler:

    'Select just the error cells in column V
    On Error GoTo NoProblems
    Set oErrorRange = Range(msgin).SpecialCells(xlCellTypeFormulas, xlErrors) 'msgin contains the cells I want to test
    oErrorRange.EntireRow.Delete
    NoProblems:
    ...

    This works for me whether there are cells with errors in column V or not.

  7. #7
    3 Star Lounger
    Join Date
    Nov 2001
    Posts
    342
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thanks Hans, but there are cells with errors in them per the enclosed screen shot. They will always be at the end of the spreadsheet, so perhaps another method would be better?
    Attached Images Attached Images

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    [quote name='Ted_Oz' post='794515' date='23-Sep-2009 10:32']Thanks Hans, but there are cells with errors in them per the enclosed screen shot. They will always be at the end of the spreadsheet, so perhaps another method would be better?[/quote]
    It shou;dn't matter whether the error cells are at the bottom.
    Could you create a stripped down copy of the workbook that demonstrates the problem and attach it to a reply? Remove or alter sensitive data.

  9. #9
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    Do the cells have formulas or are they values? The code looks for formulas that contain errors. If a copy - paste special - values was done (for example) there would be no formula errors but constant value errors...

    Steve

  10. #10
    3 Star Lounger
    Join Date
    Nov 2001
    Posts
    342
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hi Hans, I feel a bit foolish however I've noticed that the cells are text, not a formula. They have been Range->Valued

    However in this instance I would have thought that my formula stated earlier should have worked, but I get a 'mismatch' error at the line which reads 'If Cells(lngRow, 22) = msgin Then'.

    The code is:

    'Get rid of #DIV/0! errors
    Dim lngRow As Long, lngMaxRow As Long, msgin
    lngMaxRow = Range("v65536").End(xlUp).Row
    msgin = "#DIV/0!"
    For lngRow = lngMaxRow To 4 Step -1
    If Cells(lngRow, 22) = msgin Then 'This is the line that is highlighted with a mismatch error.
    Cells(lngRow).EntireRow.Delete
    End If
    Next lngRow

    Any thoughts why this does not work?
    Thanks again.

  11. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Even if you have replaced formulas with values, errors are still errors, not text values. Try the original code, but with xlCellTypeConstants instead of xlCellTypeFormulas:

    Set oErrorRange = Range(msgin).SpecialCells(xlCellTypeConstants, xlErrors)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •