CODEWITHSUNDEEP
vbaexcel
EKet
EKet

Reputation: 7314

How to NOT skip empty rows or cells in vba?

Is there any way to do a vba for each loop that loops a range but includes the empty cells/rows.

PROBLEM: When I do a copy on the row from some other worksheet, the loop automatically skips the cells in the "A" column that are blank and skips that entire row completely and therefore I lose some data in that row because ONLY the A column is blank.

eg. 

 For Each cell In wks.Range("A2", "A1000")
    cell.EntireRow.Copy Destination:=Worksheets("Master").Range("A65536").End(xlUp).Offset(1, 0)     
 Next cell

Upvotes: 0

Views: 1688

Answers (2)

Dick Kusleika
Dick Kusleika

Reputation: 33175

This will find the last used row for any column in Master and paste to the next row. Master has to have at least one cell filled or it will error

Sub test()

    Dim cell As Range
    Dim wks As Worksheet
    Dim rNext As Range
    Dim wsMaster As Worksheet

    Set wsMaster = Worksheets("Master")
    Set wks = Sheet1

    For Each cell In wks.Range("A2", "A1000")
        Set rNext = wsMaster.Cells(wsMaster.Cells.Find("*", _
            wsMaster.Range("A1"), , , , xlPrevious).Row, 1).Offset(1, 0)
        cell.EntireRow.Copy Destination:=rNext
    Next cell

End Sub

It uses the technique of FINDing the next cell prior to A1. It looks for "*" which is the wildcard for anything, and it looks for the previous cell, which should be the last cell in the spreadsheet.

Upvotes: 0

Tahbaza
Tahbaza

Reputation: 9546

I believe the problem is in the use of the offset selection in the End() method you're using. Extending a range selection like that stops when cells are empty. Try specifying your desired range rows explicitly like this.

ThisWorkbook.ActiveSheet.Range("E10:E20").Copy _
  ThisWorkbook.ActiveSheet.Range("F10:F20")

Of course hardcoding the col and row won't work for your situation and you're going to want to use your wks variable rather than the activesheet, but just replace the letter and number of the row and col with the correct values by concatenating. Something probably closer to this:

Dim fromCol as String, toCol as String, fromRow as String, toRow as String
' populate your 4 variables through your own logic here 

    wks.Range(fromCol & fromRow ":" & fromCol & toRow).Copy _
      wks.Range(toCol & fromRow & ":" & toCol & toRow)

Good luck!

Upvotes: 2

Related Questions