Results 1 to 6 of 6

Excel Macro: If cell value in range is <>"", then clear offset cells

Threaded View

  1. #1
    Registered User
    Join Date
    06-26-2018
    Location
    Perth
    MS-Off Ver
    2016
    Posts
    18

    Excel Macro: If cell value in range is <>"", then clear offset cells

    Hi everyone,

    Was hoping someone out there might be able to show me more efficient way of writing some simple VBA. Current the execution is time consuming, and the example file I have posted is only a fraction of the entire ranges I need to run the Macro on.

    The problem is this:

    I want to create a macro whereby, if the cells in column N have values in them (or not blank), the adjacent columns O -> S are cleared. Given I am running the code over multiple spreadsheets and thousands of rows, it is taking approximately 2 mins 30 sec to execute the code. Kinda defeats the purpose of automating the task when I could do it manually in less time.

    Very interested to hear your thoughts and suggestions

    Here is my current code:
    Sub clearcells()
    
    Dim rng As Range
    Set rng = Range("N4:N181")
        For Each cell In rng
            'test if cell is empty
            If cell.Value <> "" Then
                'write to adjacent cell
                cell.Offset(0, 1).Value = ""
                cell.Offset(0, 2).Value = ""
                cell.Offset(0, 3).Value = ""
                cell.Offset(0, 4).Value = ""
                cell.Offset(0, 5).Value = ""
            End If
        Next
    End Sub
    Attached Files Attached Files
    Last edited by Jackson2806; 12-31-2018 at 08:56 PM.

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Trying to add a macro to activate another macro to "click" a command button to clear cells
    By nthpl888 in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 05-13-2016, 02:52 PM
  2. [SOLVED] Event to check cells in Range("I207:I216") contains number ealse clear contents
    By mangesh.mehendale in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 09-12-2015, 12:07 AM
  3. VBA if cell value(s) in range ="1/0/1900" clear contents
    By Tom_Watson123 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 06-08-2015, 07:38 PM
  4. [SOLVED] A "No" response in Cell A1 populates a range of cells (A2:A10) as "N/A"
    By Dutch01 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 02-24-2015, 06:43 PM
  5. Clear content of range of cells if value is ""
    By ron2k_1 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 03-07-2013, 12:59 PM
  6. Replies: 12
    Last Post: 10-15-2012, 03:57 PM
  7. [SOLVED] If Cell in column = "Y" then with offset (0, 1).value match to sheet range A3:A return ...
    By Spyderz in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 10-13-2012, 02:00 PM

Tags for this Thread

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