excelvbaselect

Selecting all non-blank cells in variable range in Excel to export


I have a data set in Excel. Starting at column C, it has continuous data from C2 to a number that changes every time it is used.

For every row of continuous data in column D, I want to select column C-J's rows as well. I'm trying to select this data to create a new Excel workbook with that data.

When I run:

Dim lastVal As Range, sht As Worksheet

Set sht = Sheets("sheet1")

Set lastVal = sht.Columns(4).Find("*", sht.Cells(1, 48), xlValues, _
                                xlPart, xlByColumns, xlPrevious)

Debug.Print lastVal.Address

sht.Range("C2", lastVal).Resize(, 48).Select
selection.copy

Set Objexcel = CreateObject("Excel.application")
objexcel.visible = True
objexcel.cells(1, 1).paste

I get:

run-time error '13': type mismatch

on the Set LastVal line.


Solution

  • Something like this:

    Sub Tester()
        Dim sht As Worksheet, wb As Workbook, shtNew As Worksheet
        
        Set sht = ThisWorkbook.Sheets("sheet1") 'or activeworkbook
        
        Set wb = Workbooks.Add() 'add new workbook
        Set shtNew = wb.Worksheets(1)
    
        'copy from Col C, using last row from Col D?
        'not sure if that's really what you want though?
        With sht.Range("C2:J" & sht.Cells(Rows.Count, "D").End(xlUp).Row)
            Debug.Print "copying", .Address
            'straight copy
            .Copy shtNew.Range("A1")
            '...or copy values only
            shtNew.Range("A1").Resize(.Rows.Count, .Columns.Count).Value = .Value
        End With
    End Sub
    

    In place of sht.Cells(Rows.Count, "D") you can use any other column in which you don't have any blanks.