CODEWITHSUNDEEP
vbaexcel
makerofthings7
makerofthings7

Reputation: 61463

Selecting columns that have values in Excel Macro (range object in VBA)

How do I modify this line in VBA to only select the columns that have values?

Set rng = Range("A1", Range("A65536").End(xlUp)).SpecialCells(xlCellTypeVisible)

I don't think I'm doing something right since the CountLarge property is several billion cells

Here is a sample of my data

enter image description here

Upvotes: 1

Views: 12895

Answers (1)

Siddharth Rout
Siddharth Rout

Reputation: 149305

@SiddharthRout Yes I only need the rows that have data. I think I have it working now with End(xlToLeft) from @JMax ... Now that I'm iterating over the cells, I can just quit the For each loop once the last row is reached. I might have this working now. – makerofthings7 14 mins ago

For this neither you need .SpecialCells nor do you need to loop through the rows :)

Here is a sample code. This will copy all the rows which have data to Sheet2 (TRIED AND TESTED)

Sub Sample()
    Dim ws As Worksheet
    Dim rng As Range
    Dim LastRow As Long, LastCol As Long

    Set ws = Sheets("Sheet1")

    With ws
        LastRow = .Cells.Find(What:="*", After:=.Range("A1"), Lookat:=xlPart, _
        LookIn:=xlFormulas, SearchOrder:=xlByRows, SearchDirection:=xlPrevious, MatchCase:=False).Row

        LastCol = .Cells.Find(What:="*", After:=.Range("A1"), Lookat:=xlPart, _
        LookIn:=xlFormulas, SearchOrder:=xlByColumns, SearchDirection:=xlPrevious, _
        MatchCase:=False).Column

        With .Range("A1:" & Split(Cells(, LastCol).Address, "$")(1) & LastRow)
            .AutoFilter Field:=1, Criteria1:="<>"
            Set rng = ws.AutoFilter.Range
            rng.Offset(1, 0).Resize(rng.Rows.Count - 1).Copy _
            Destination:=Sheets("Sheet2").Range("A1")
        End With
    End With
End Sub

SNAPSHOT

enter image description here

I am assuming that all cells in a particular row will have data and there won't be a case like this

@makerofthings7: I think I know what exactly you are trying to do :) you don't need to use loops to achieve what you want. Just a quick question. Is it possible that say Cell C10 might have a value but B10 might not? – Siddharth Rout 12 mins ago

If there is then we will have to set the autofilter criteria accordingly.

Edit: WAY 2

The other way would be to sort your data, pushing the blanks way down and then copying the resulting range :)

HTH

Upvotes: 3

Related Questions