Results 1 to 6 of 6

Macro to use array to copy all rows to a new sheet if a cell is not blank not working

Threaded View

  1. #1
    Forum Contributor
    Join Date
    07-11-2010
    Location
    Minneapolis, USA
    MS-Off Ver
    Excel 2016
    Posts
    308

    Macro to use array to copy all rows to a new sheet if a cell is not blank not working

    I am trying to a use array to copy all rows to a new sheet if a cell is not blank.

    I know there are other methods to do this but was trying to get a better hand with arrays but am failing here

    Right now the code copies the header row multiple times and moves all the content

    Thanks for any help or insight


    Sub MoveRowIfCellIsNotEmpty()
    Dim myArray() As Variant
    Dim tempArray() As Variant
    Dim Destination As Range
    Dim wsS As Worksheet
    Dim wsT As Worksheet
    Dim LC As Long
    Dim LR As Long
    Dim i As Long
    Dim j As Long
    
    Set wsS = ThisWorkbook.Sheets("NodeFile")
    Set wsT = ThisWorkbook.Sheets("XXX")
    
    With wsS
         LC = (.Cells(1, .Columns.Count).End(xlToLeft).Column)
         LR = (.Range("A" & .Rows.Count).End(xlUp).Row)
      myArray = .UsedRange
    End With
    
    j = 1
    For i = 1 To LR - 1
         If myArray(i, 4) = "" Then 'if cell is NOT blank paste ROW to new sheet
             Set Destination = wsT.Range("A" & j)
               Destination.Resize(UBound(myArray, 1), UBound(myArray, 2)).Value = myArray
            
            j = j + 1
         End If
    Next
    
    End Sub
    Attached Files Attached Files

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. How to create a macro to insert blank rows and copy data into blank rows?
    By zodiack101 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 10-29-2013, 01:18 PM
  2. macro to copy changing range to next blank cell in another sheet
    By d2whales in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 07-09-2013, 12:55 PM
  3. Macro to copy rows to another sheet, based on cell value
    By hampton06 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 08-03-2012, 04:40 AM
  4. Copy rows from master sheet to other sheets if certain cell in column is blank
    By nadiaraciti in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 07-29-2011, 02:05 AM
  5. Macro to copy rows from 1 sheet into the next blank row on a different sheet
    By kpratico in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 04-13-2010, 01:16 PM

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