Results 1 to 13 of 13

Removing blanks where blanks show blank but are code to show blank.

Threaded View

  1. #1
    Forum Contributor
    Join Date
    03-25-2011
    Location
    London, England
    MS-Off Ver
    office 2021
    Posts
    318

    Removing blanks where blanks show blank but are code to show blank.

    Hi Guys
    I have the following code which was kindly given to me by a colleague at ExcelForum. It was designed to remove one row if that row was empty which it does. I have tried to use it in another location where there are several hundred blank rows which it was not designed for and takes a very long time removing some. Eventually having moved some it gives up.
    Firstly I can’t work out why it is removing some as they appear identical to the ones not removed
    Secondly it is very slow.
    So is there any code that would remove these blanks.
    Here is the code I have

    'This will remove the blank row only if there is one
    'Sub checkTwoCols()
    'Option Explicit
    
        Dim cel As Range, rng As Range
         Set rng = Range("A2", Range("A65536").End(xlUp))
          For Each cel In rng
            If cel.Value = "" Then
                If cel.Offset(, 2).Value = "" Then
                   cel.EntireRow.Delete
                End If
           End If
        Next cel
    End Sub

    The problem is that I have now run a macro to pull from another sheet the data I need. The macro shows “ “ in the cells I do not need but behind that there is the code. I do not know how Excel recognises this. If anyone has a suggestion I would be thrilled.
    I am uploading the workbook which puts my comments into context. The sheet I am working in is Petty cash and the data is being pulled from BDC2. The macro (4) has just been run. There are entries in the petty cash sheet. Some of the columns are hidden because there are not needed.

    Thanks in advance
    Mark
    Attached Files Attached Files

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. VBA Code to Copy Non Blanks/Cells with Values until Formula returns as blank
    By FLani in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 04-16-2013, 10:18 PM
  2. [SOLVED] Find first non-blank cell after a group of blanks copy offset cells, find next blank, loop
    By gwsampso in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 10-21-2012, 06:11 AM
  3. Replies: 4
    Last Post: 08-29-2012, 02:45 PM
  4. Replies: 1
    Last Post: 04-26-2008, 08:00 AM
  5. [SOLVED] Hide show blanks
    By PCOR in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 05-26-2005, 05:19 PM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1