Results 1 to 25 of 25

delete blank rows in new Workbook with VBA

Threaded View

  1. #1
    Registered User
    Join Date
    04-24-2020
    Location
    Stourbridge, England
    MS-Off Ver
    365
    Posts
    29

    delete blank rows in new Workbook with VBA

    Hi all, this is driving me completely crazy, i'm far from a vba expert, hope you can help.

    So I have a workbook open with the following code behind a form control button.
    It's supposed to create,name & save a new workbook and copy a range to it from a sheet (with a frozen pane) called MOM no named wsI in the code (stands for method of manufacture), then remove any blank rows.
    I cant get it to remove the blank rows, i get a variation of error messages based around subscript out of range. I mention the frozen panes because It did briefly in a flaky way when i played around with freezing/unfreezing panes, but not robust enough to rely on, then it stopped.
    It seems that when i create & save the new workbook (wbo) it is the active workbook as it is ontop of the original & is in focus but if i add a message box to return name of active workbook it shows (wbi). But whatever i do to try to make the right sheet active at the right time it doesn't work.

    I don't mind going about it an entirely different way if anyone can suggest, all i want is a new workbook with the blank rows removed.

    Many thanks in advance

    VBA Code:
    Sub CopyToNewBook()
    
    Dim wbI As Workbook, wbO As Workbook
    Dim wsI As Worksheet, wsO As Worksheet
    Dim loc As Range
    Dim DateTime As String
    Dim Spath As String
    Dim User As String
    Dim r As Range, rows As Long, i As Long
       
          
    DateTime = Format(CStr(Now), "ddmmyyyy" & " " & "hhmmss")
    Set loc = Range("k2") 'contains the filepath to save to
    User = Environ("Username") & " " & "Backup" & " "
    Spath = loc & "\" & User & DateTime
    
    '~~> Source/Input Workbook
    Set wbI = ThisWorkbook
    
    '~~> Set the relevant sheet from where you want to copy
    Set wsI = wbI.Sheets("Clipboard")
    
    '~~> Destination/Output Workbook
    Set wbO = Workbooks.Add
    
    With wbO
            '~~> Set the relevant sheet to where you want to paste
            Set wsO = wbO.Sheets("Sheet1")
    
            '~~>. Save the file
            .SaveAs Filename:=Spath & ".XLSX", FileFormat:=56
    
            '~~> Copy the range
            wsI.Range("c1:eek:549").Copy
    
            '~~> Paste it in say Cell A1.
            wsO.Range("A1").PasteSpecial Paste:=xlPasteValues
           
            Application.CutCopyMode = False
           
                   
        'remove blank rows
       
        Set r = wbO.Worksheets("Sheet1").Range("a1:m549")
        rows = r.rows.Count
        For i = rows To 1 Step (-1)
        If WorksheetFunction.CountA(r.rows(i)) = 0 Then r.rows(i).Delete
        Next
       
    End With
    
    End Sub
    Last edited by QuantumSquirrel; 04-24-2020 at 10:27 AM.

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Replies: 1
    Last Post: 07-31-2019, 01:32 PM
  2. [SOLVED] Delete blank rows between blocks of data except two blank rows
    By YasserKhalil in forum Excel Programming / VBA / Macros
    Replies: 13
    Last Post: 03-26-2018, 11:18 AM
  3. How do i delete blank rows from every worksheet in a workbook?
    By naomip in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 11-08-2017, 06:47 AM
  4. Using code to delete rows with blank cells in them upon opening the workbook
    By marielouise1 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 02-12-2014, 02:00 PM
  5. Replies: 3
    Last Post: 07-17-2013, 01:25 AM
  6. [SOLVED] How to Delete Multiple Consecutive Blank Rows - Delete all Blank Rows
    By raw_geek in forum Excel Programming / VBA / Macros
    Replies: 18
    Last Post: 11-16-2012, 03:17 PM
  7. [SOLVED] Delete blank rows after filter - if no blank rows exit sub
    By cmb80 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 06-27-2012, 10:00 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