+ Reply to Thread
Results 1 to 3 of 3

If Range Is Nothing Then...

  1. #1
    Registered User
    Join Date
    01-11-2021
    Location
    Brighton
    MS-Off Ver
    365
    Posts
    85

    If Range Is Nothing Then...

    Trying to search the column with 'TOC' as the header and alter the size, which works fine. The problem I'm having is I no longer want to have an error message pop up if the cells are empty or contain other values - I just want it to ignore and move on to the next task. Thanks in advance of your help.
    Example spreadsheet attached sheet 1 with TOC column and sheet 2 without. Will load shortly as Safari not letting me load!


    Sub TOC_width()
    Dim rng As Range, w As Long

    Set rng = Rows(1).Find(What:="TOC", LookAt:=xlWhole, LookIn:=xlFormulas)
    If rng Is Nothing Then
    MsgBox "TOC", vbCritical
    Else
    Select Case rng.Column

    'Case = column number i.e. A=1, B=2 etc...
    'w = column width

    Case 1
    w = 3
    Case 2
    w = 3
    Case 3
    w = 3

    Case Else
    w = rng.ColumnWidth
    End Select
    rng.ColumnWidth = w
    End If
    End Sub

  2. #2
    Forum Expert rorya's Avatar
    Join Date
    08-13-2008
    Location
    East Sussex, UK
    MS-Off Ver
    365 Ent Monthly Channel / Insiders Beta
    Posts
    8,913

    Re: If Range Is Nothing Then...

    Just remove the Msgbox line.
    Rory

  3. #3
    Registered User
    Join Date
    01-11-2021
    Location
    Brighton
    MS-Off Ver
    365
    Posts
    85

    Re: If Range Is Nothing Then...

    Thanks rorya - thought I'd tried that, but obviously not.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Use range.copy_ destination:= ... instead of range.copy range.paste is not working. Help!
    By Halil93 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 02-26-2020, 03:32 PM
  2. Copy range A of cells if value is in range B & paste in range B/Multiple Or
    By Jeremycad in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 10-22-2018, 04:28 AM
  3. Replies: 4
    Last Post: 02-24-2017, 02:06 AM
  4. Replies: 9
    Last Post: 05-19-2016, 06:48 PM
  5. [SOLVED] Help! - Copy Range (Sheet1,A10:F50), Paste Range (Sheet 2,A1:F41), Offset Range & Repeat
    By cjtimmer in forum Excel Programming / VBA / Macros
    Replies: 11
    Last Post: 09-30-2015, 06:27 PM
  6. [SOLVED] Count number of occurances below a range, within a range, and below a range
    By maacmaac in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 09-15-2013, 11:36 PM
  7. send to range, popup box to input what the range should be each time/select range
    By lax2734 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 08-26-2012, 01:37 AM

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