Results 1 to 6 of 6

Isolate the last 3 used area of the column

Threaded View

  1. #1
    Forum Contributor
    Join Date
    05-16-2011
    Location
    Philippines
    MS-Off Ver
    Excel 365/Excel 2016
    Posts
    315

    Isolate the last 3 used area of the column

    Hello, few years back. I do have problem finding last empty cell on a column and inserting, copy paste the last non empty column. Now, I do have same problem again but this time I have to copy the last 3 non empty columns and paste it on 3 inserted column. Here is the piece of successful code with my previous problem. Hope you can modify it. Thanks!

    Sub AddCol()
        Dim rng As Range, c As Range
        Set rng = Intersect(ActiveSheet.UsedRange, Rows(2)) ' Isolate row 2
        If rng Is Nothing Then Exit Sub ' Assuming there is more than one row
        Set c = rng.Cells(rng.Cells.Count) ' Isolate the last cell in row 2
        If IsEmpty(c) Then Set c = c.End(xlToLeft) ' Find the last non-empty cell
        If Not IsEmpty(c) Then
            Set rng = Intersect(c.EntireColumn, ActiveSheet.UsedRange) ' Isolate the used area of the column
            rng.Copy ' Copy to clipboard
            rng.Insert Shift:=xlToRight ' Duplicate
            c.Offset(0, -1).Select ' Select the "marker" in the duplicate column
            Application.CutCopyMode = False ' Switch off the CutCopy marquee
        End If
    End Sub
    Last edited by emina002; 03-07-2014 at 03:51 AM.

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Isolate sum from cumulative sums in a column
    By nrrrrb in forum Excel General
    Replies: 4
    Last Post: 07-29-2013, 10:44 AM
  2. Isolate rows based on first column
    By justinsmyth1 in forum Excel General
    Replies: 5
    Last Post: 06-22-2012, 06:46 PM
  3. Isolate Duplicates in Column B if Column A also Duplicate
    By Ocean Zhang in forum Excel General
    Replies: 4
    Last Post: 03-13-2012, 04:26 PM
  4. Tuning column chart to get area-column effect
    By goels in forum Excel General
    Replies: 2
    Last Post: 06-07-2011, 01:02 PM
  5. Isolate 1st 2 letters in a column
    By Curalice in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 02-06-2007, 04:49 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