Results 1 to 3 of 3

detecting blank range, unmerge, paste then remerge

Threaded View

  1. #1
    Forum Contributor
    Join Date
    02-09-2004
    Posts
    154

    detecting blank range, unmerge, paste then remerge

    basically i have some code below which checks if theres still room on the MWR Template sheet by looking if B32 to H32 is blank. If it is blank then it copies the current row column A and B onto that sheet into cols B and C on the first available blank row on that sheet. If there is no blank row left then it adds a new one at row A9 and paste into that instead.

    The issue is i need to merge columns B to H on the MWR Template sheet as users also manually type things sometimes and currently i have had to unmerge it so that the paste operation works without comlpaining about merged cells. So basically what i want to do is modify the current code so that once it has identified which row it wants to paste the 2 cells into on the MWR Template sheet, it will unmerge columns B to H on that row, paste in the data, then re-merge them. This way i can leave it merged by default for users to have plenty of space to type in, and the macro still pastes in without any errors. I am not sure how i can modify the code to do this though. I cannot do this by not using merged cells and simply widening the column because its a form and the extra columns are required at the top.

    Currently its a bit crude in that i have the cells unmerged and am centering accross the selection to achieve the same effect, however its still going to cause issues for dumb end users who type into the wrong column because they are not merged. hence i need to merge B to H and still be able to paste into it.

    With ActiveSheet
            If Application.WorksheetFunction.CountA(Worksheets("MWR Template").Range("b32:h32")) = 0 Then
                'space
                .Range(.Cells(ActiveCell.Row, 1), .Cells(ActiveCell.Row, 2)).Copy Worksheets("MWR Template").Range("B32:H32").End(xlUp)(2)
            Else
                'no space
                Worksheets("MWR Template").Range("A9").EntireRow.Insert
                .Range(.Cells(ActiveCell.Row, 1), .Cells(ActiveCell.Row, 2)).Copy Worksheets("MWR Template").Range("B9")
            End If
            Worksheets("MWR Template").Range("C8:H32").HorizontalAlignment = xlCenterAcrossSelection
        End With
    Last edited by neowok; 10-01-2009 at 09:53 AM.

Thread Information

Users Browsing this Thread

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

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