+ Reply to Thread
Results 1 to 3 of 3

How to run multiple subs in multiple ranges within a worksheet

Hybrid View

roshanvmech How to run multiple subs in... 03-05-2014, 11:55 AM
StephenR Re: How to run multiple subs... 03-05-2014, 12:18 PM
roshanvmech Re: How to run multiple subs... 03-05-2014, 02:19 PM
  1. #1
    Registered User
    Join Date
    05-06-2013
    Location
    bangalore, india
    MS-Off Ver
    Excel 2010
    Posts
    28

    How to run multiple subs in multiple ranges within a worksheet

    Hi All,
    I have a worksheet divided into 4 quadrants. In each quadrant for a selected range, I have to perform the below activities.
    In Q1 (A4:B23): All the empty cells should go at the bottom. i.e, if A9:B9 is empty, then the rows below should move up pushing all the empty cells to the bottom within the Q1.
    In Q2 (D4: AE12): All cells except the last column should not have any background color and if any cell is empty in row D8:AE8, the entire column should be deleted.
    In Q3 (A25:B75): All cells should not have any background color and if any cell is empty in range A25:A75, the entire row should be deleted.
    In Q4 (D25:AE75): The cell with any alphabet should have the color which is given in Q1 for that alphabet. Eg: "A" in Q1 is pink so all cells which have "A" in Q4 should have background color as pink.
    Note: The ranges mentioned above varies from document to document.
    I have written a code for Q2,Q3 as below but I am getting a run time error.
    Sub CleanRoadMap()
    ' To clear cell color
    Dim C As Range, ws As Worksheet
    For Each ws In ActiveWorkbook.Worksheets
    
    If ws.Name = "Roadmap" Then
              Call ClearCell(ws, C)
               End If
    
    Next ws
    End Sub
    
    Sub ClearCell(ByVal ws As Worksheet, ByVal C As Range)
    
               N = ws.Cells.Find("SUBID", , , , xlByRows, xlPrevious).Row
               LR = Cells(Rows.Count, "A").End(xlUp).Row
               M = ws.Cells.Find("None", , , , xlByColumns, xlPrevious).Column
               S = ws.Cells.Find("SCRN", , , , xlByRows, xlPrevious).Row
               For Each C In ws.Range(Cells(N, 1), Cells(LR, 1))
               C.SpecialCells(xlCellTypeBlanks).EntireRow.Delete
               C.Interior.ColorIndex = x1None
               Next C
               For Each C In ws.Range(Cells(N, 2), Cells(LR, 2))
               C.Interior.ColorIndex = x1None
               Next C
               For Each C In ws.Range(Cells(4, 4), Cells(S - 1, M - 1))
               C.Interior.ColorIndex = x1None
               Next C
               For Each C In ws.Range(Cells(S, 4), Cells(S, M - 1))
               C.SpecialCells(xlCellTypeBlanks).EntireColumn.Delete
               C.Interior.ColorIndex = x1None
               Next C
               For Each C In ws.Range(Cells(S + 1, 4), Cells(S + 1, M - 1))
               C.Interior.ColorIndex = x1None
               Next C
               For Each C In ws.Range(Cells(4, 2), Cells(N - 2, 2))
               C.Interior.ColorIndex = x1None
               Next C
    End Sub

    Please help!!!
    Attached Files Attached Files
    Last edited by roshanvmech; 03-05-2014 at 12:03 PM.

  2. #2
    Forum Guru
    Join Date
    08-26-2007
    Location
    London
    Posts
    4,606

    Re: How to run multiple subs in multiple ranges within a worksheet

    Which line errors?

    In your calling macro, you don't pass any range, or at least you haven't defined C.

  3. #3
    Registered User
    Join Date
    05-06-2013
    Location
    bangalore, india
    MS-Off Ver
    Excel 2010
    Posts
    28

    Re: How to run multiple subs in multiple ranges within a worksheet

    C.SpecialCells(xlCellTypeBlanks).EntireRow.Delete
    This one throws an error stating Run Time Error : 1004 Cannot use that command on overlapping selections.
    I get the same error even if I directly put the code within the if clause and not by calling subroutines. But if I run the for loops individually it is working.

+ 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. Nesting Multiple Subs
    By cp41 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 09-30-2013, 03:58 PM
  2. Help with multiple subs in one worksheet
    By leemcilwraith in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 08-20-2012, 11:03 AM
  3. Printing multiple ranges in multiple worksheet into a single printout
    By frousseau in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 08-16-2012, 08:53 AM
  4. Multiple Private subs
    By LouiseH24 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 12-14-2011, 01:18 PM
  5. [SOLVED] Exiting Multiple Subs?
    By SS in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 06-02-2005, 04:05 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