CODEWITHSUNDEEP
vbaexcel
Meedee
Meedee

Reputation: 127

Select all cells with value from specific row

I'm trying to copy all cells with value in it from 2nd row 'coz 1st row is the heading/title. However, my code seems to select ALL cells with value. :(
Here's my code to copy:

Dim myData As Workbook
Set myData = Workbooks.Open("C:\Book2.xlsx")

Worksheets("Sheet1").Range("A2").Select
Worksheets("Sheet1").Range("A2").CurrentRegion.Copy

Upvotes: 1

Views: 213

Answers (2)

Siddharth Rout
Siddharth Rout

Reputation: 149287

Even though you have mentioned CurrentRegion in your code, I suspect you do not need it or at least are not planning to use it in the intended way

Let's say our worksheet looks like this. Notice the formulas in Col F?

enter image description here

The problem with using .Resize is that it will include cells which has formulas even if they return blank values.

Taking MacroMan's example

Sub Sample()
    With Sheets("Sheet1").Range("A2").CurrentRegion.Offset(1, 0)
        .Resize(.Rows.Count - 1, .Columns.Count).Select
    End With
End Sub

enter image description here

I'm trying to copy all cells with value in it from 2nd row 'coz 1st row is the heading/title.

From what I understand, what you want can be achieved by finding the last row and last column which has data and copying that.

I think this is what you want? Notice the use of LookIn:=xlValues with .Find

Sub Sample()
    Dim ws As Worksheet
    Dim rng As Range
    Dim lRow As Long, lCol As Long

    Set ws = ThisWorkbook.Sheets("Sheet1")

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

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

        Set rng = .Range(.Cells(2, 1), .Cells(lRow, lCol))

        Debug.Print rng.Address
        rng.Copy
    End With
End Sub

enter image description here

Upvotes: 6

SierraOscar
SierraOscar

Reputation: 17637

You will need to offset and resize:

With Sheets("Sheet1").Range("A2").CurrentRegion.Offset(1, 0)
    .Resize(.Rows.Count - 1, .Columns.Count).Copy
End With

Upvotes: 2

Related Questions