Find first blank cell in column A in a Table - Runtime error 91

willow1985

willow1985

Well-known Member
Joined
Jul 24, 2019
Messages
929
Office Version
  1. 365
Platform
  1. Windows
Hello,

I am looking for help on the below code. This code used to work on my spreadsheet but now it gives me a Runtime error 91 on line:

For Each cell In ws.Columns(1).Cells

This part of the code is not my own, I found this method online on how to select the first blank cell in column 1 in a table (Table name = PPAP) but now it does not seem to be working.

Any help would be greatly appreciated.

VBA Code: 
Sub PPAPData()
'
' PPAPData Macro
'
Dim PPD As String
Dim PPN As String
PPD = Workbooks("Eng_QA Dashboard").Sheets("Graph Data").Range("B38").Value
PPN = Workbooks("Eng_QA Dashboard").Sheets("Graph Data").Range("B39").Value

Sheets("PPAPs").Select

    ActiveSheet.ListObjects("PPAP").Resize Range("$A$1:$O$2")
    Rows("3:1000").Select
    Selection.ClearContents
Range("A2").Select
    ActiveCell.Range("PPAP[[#Headers],[Ppap Identifier]:[Comments]]").Select
    Selection.ClearContents
   
Workbooks.Open PPD

'Grab PPAP Data
Windows(PPN).Activate

Sheets("PPAPs").Select
  For Each lo In ActiveSheet.ListObjects
   lo.AutoFilter.ShowAllData
      Next lo
      If (ActiveSheet.AutoFilterMode And ActiveSheet.FilterMode) Or ActiveSheet.FilterMode Then
  ActiveSheet.ShowAllData
End If
Cells.EntireColumn.Hidden = False

    ActiveSheet.ListObjects("PPAP").Range.AutoFilter Field:=5, Criteria1:=11, _
        Operator:=11, Criteria2:=0, SubField:=0
If ActiveSheet.ListObjects("PPAP").Range.Columns(1).SpecialCells(xlCellTypeVisible).Count = 1 Then
GoTo NextPP
End If
Range("A2").Select
ActiveSheet.ListObjects("PPAP").DataBodyRange.Columns("A:G").Select
    Selection.SpecialCells(xlCellTypeVisible).Copy
    Windows("Eng_QA Dashboard.xlsm").Activate
Sheets("PPAPs").Select
     Dim ws As Worksheet
    Set ws = ActiveSheet
    For Each cell In ws.Columns(1).Cells 'the line that is giving me the error
        If IsEmpty(cell) = True Then cell.Select: Exit For
    Next cell
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("A1").Select
    Application.CutCopyMode = False
NextPP:
Windows(PPN).Activate
Sheets("PPAPs").Select
For Each lo In ActiveSheet.ListObjects
   lo.AutoFilter.ShowAllData
      Next lo
      If (ActiveSheet.AutoFilterMode And ActiveSheet.FilterMode) Or ActiveSheet.FilterMode Then
  ActiveSheet.ShowAllData
End If
Cells.EntireColumn.Hidden = False

    ActiveSheet.ListObjects("PPAP").Range.AutoFilter Field:=5, Criteria1:=10, _
        Operator:=11, Criteria2:=0, SubField:=0
If ActiveSheet.ListObjects("PPAP").Range.Columns(1).SpecialCells(xlCellTypeVisible).Count = 1 Then
GoTo NextPP2
End If

Range("A1").Select
ActiveSheet.ListObjects("PPAP").DataBodyRange.Columns("A:G").Select
    Selection.SpecialCells(xlCellTypeVisible).Copy
    Windows("Eng_QA Dashboard.xlsm").Activate
Sheets("PPAPs").Select
    For Each cell In ws.Columns(1).Cells
        If IsEmpty(cell) = True Then cell.Select: Exit For
    Next cell
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("A1").Select
    Application.CutCopyMode = False
NextPP2:

Windows(PPN).Activate
Sheets("PPAPs").Select
For Each lo In ActiveSheet.ListObjects
   lo.AutoFilter.ShowAllData
      Next lo
      If (ActiveSheet.AutoFilterMode And ActiveSheet.FilterMode) Or ActiveSheet.FilterMode Then
  ActiveSheet.ShowAllData
End If
Cells.EntireColumn.Hidden = False

    ActiveSheet.ListObjects("PPAP").Range.AutoFilter Field:=5, Criteria1:="="
    ActiveSheet.ListObjects("PPAP").Range.AutoFilter Field:=4, Criteria1:=11, _
        Operator:=11, Criteria2:=0, SubField:=0
If ActiveSheet.ListObjects("PPAP").Range.Columns(1).SpecialCells(xlCellTypeVisible).Count = 1 Then
GoTo NextPP3
End If

Range("A1").Select
ActiveSheet.ListObjects("PPAP").DataBodyRange.Columns("A:G").Select
    Selection.SpecialCells(xlCellTypeVisible).Copy
    Windows("Eng_QA Dashboard.xlsm").Activate
Sheets("PPAPs").Select
    For Each cell In ws.Columns(1).Cells
        If IsEmpty(cell) = True Then cell.Select: Exit For
    Next cell
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("A1").Select
    Application.CutCopyMode = False
NextPP3:

Windows(PPN).Activate
Sheets("PPAPs").Select
For Each lo In ActiveSheet.ListObjects
   lo.AutoFilter.ShowAllData
      Next lo
      If (ActiveSheet.AutoFilterMode And ActiveSheet.FilterMode) Or ActiveSheet.FilterMode Then
  ActiveSheet.ShowAllData
End If
Cells.EntireColumn.Hidden = False

    ActiveSheet.ListObjects("PPAP").Range.AutoFilter Field:=5, Criteria1:="="
    ActiveSheet.ListObjects("PPAP").Range.AutoFilter Field:=4, Criteria1:=10, _
        Operator:=11, Criteria2:=0, SubField:=0
If ActiveSheet.ListObjects("PPAP").Range.Columns(1).SpecialCells(xlCellTypeVisible).Count = 1 Then
GoTo NextPP4
End If
Range("A1").Select
ActiveSheet.ListObjects("PPAP").DataBodyRange.Columns("A:G").Select
    Selection.SpecialCells(xlCellTypeVisible).Copy
    Windows("Eng_QA Dashboard.xlsm").Activate
Sheets("PPAPs").Select
    For Each cell In ws.Columns(1).Cells
        If IsEmpty(cell) = True Then cell.Select: Exit For
    Next cell
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("A1").Select
    Application.CutCopyMode = False
NextPP4:
Windows(PPN).Activate
Sheets("PPAPs").Select
For Each lo In ActiveSheet.ListObjects
   lo.AutoFilter.ShowAllData
      Next lo
      If (ActiveSheet.AutoFilterMode And ActiveSheet.FilterMode) Or ActiveSheet.FilterMode Then
  ActiveSheet.ShowAllData
End If
Cells.EntireColumn.Hidden = False

    ActiveSheet.ListObjects("PPAP").Range.AutoFilter Field:=5, Criteria1:="="
    ActiveSheet.ListObjects("PPAP").Range.AutoFilter Field:=4, Criteria1:=12, _
        Operator:=11, Criteria2:=0, SubField:=0
If ActiveSheet.ListObjects("PPAP").Range.Columns(1).SpecialCells(xlCellTypeVisible).Count = 1 Then
GoTo NextPP5
End If
Range("A1").Select
ActiveSheet.ListObjects("PPAP").DataBodyRange.Columns("A:G").Select
    Selection.SpecialCells(xlCellTypeVisible).Copy
    Windows("Eng_QA Dashboard.xlsm").Activate
Sheets("PPAPs").Select
    For Each cell In ws.Columns(1).Cells
        If IsEmpty(cell) = True Then cell.Select: Exit For
    Next cell
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("A1").Select
    Application.CutCopyMode = False
NextPP5:
Workbooks(PPN).Close False
    Windows("Eng_QA Dashboard.xlsm").Activate
End Sub

1713192850473.jpeg
 

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.
Sort by date Sort by votes
The line of code For Each cell In ws.Columns(1).Cells appears 5 times in your subroutine. Is it the first occurrence that produces the error or is it another one?
 
Upvote 0
rlv01 said:
The line of code For Each cell In ws.Columns(1).Cells appears 5 times in your subroutine. Is it the first occurrence that produces the error or is it another one?
Click to expand...
It is actually the 3rd occurrence. I thought it might be the 3rd because the first 2 did not have any filtered data to copy into the table (table name: PPAP) so it is supposed to skip if there are no filtered results to copy.

The sheet it is on (with the table) when the error occurs is below:

Eng_QA Dashboard.xlsm
ABCDEFGHIJKLMNO
1Ppap IdentifierFamilyCustomerDue DateDate CompletedReference DateCommentsLate Y/NMonth DueYear DueMonth CompletedYear Completed90 Day MarkerDue in 90 daysCompleted in 90
2YJanuary1900  ##############LateNot Completed to Date
PPAPs
Cell Formulas
RangeFormula
H2H2=IF(D2<E2,"Y",IF(AND(E2="",D2<TODAY()),"Y","N"))
I2I2=TEXT(D2,"mmmm")
J2J2=YEAR(D2)
K2K2=IF(E2="","",TEXT(E2,"mmmm"))
L2L2=IF(E2="","",YEAR(E2))
M2M2=[@[Due Date]]-90
N2N2=IF([@[Date Completed]]="",IF(AND('Graph Data'!$A$1>=[@[90 Day Marker]],'Graph Data'!$A$1<=[@[Due Date]]),"Yes","Late"),"Completed")
O2O2=IF([@[Date Completed]]<>"",IF(AND([@[Date Completed]]>=[@[90 Day Marker]],[@[Date Completed]]<=[@[Due Date]]),"Yes","No"),"Not Completed to Date")
 
Upvote 0
Only the first instance has Set ws = ActiveSheet before the For loop. I think you should try adding it to the other occurences, that is

VBA Code: 
Sheets("PPAPs").Select
For Each Cell In ws.Columns(1).Cells
    If IsEmpty(Cell) = True Then Cell.Select: Exit For
Next Cell

becomes

VBA Code: 
Sheets("PPAPs").Select
Set ws = ActiveSheet
For Each Cell In ws.Columns(1).Cells
    If IsEmpty(Cell) = True Then Cell.Select: Exit For
Next Cell

or else just

VBA Code: 
Sheets("PPAPs").Select
For Each Cell In ActiveSheet.Columns(1).Cells
    If IsEmpty(Cell) = True Then Cell.Select: Exit For
Next Cell
 
Upvote 0
Solution
rlv01 said:
Only the first instance has Set ws = ActiveSheet before the For loop. I think you should try adding it to the other occurences, that is

VBA Code: 
Sheets("PPAPs").Select
For Each Cell In ws.Columns(1).Cells
    If IsEmpty(Cell) = True Then Cell.Select: Exit For
Next Cell

becomes

VBA Code: 
Sheets("PPAPs").Select
Set ws = ActiveSheet
For Each Cell In ws.Columns(1).Cells
    If IsEmpty(Cell) = True Then Cell.Select: Exit For
Next Cell

or else just

VBA Code: 
Sheets("PPAPs").Select
For Each Cell In ActiveSheet.Columns(1).Cells
    If IsEmpty(Cell) = True Then Cell.Select: Exit For
Next Cell
Click to expand...

I completely missed this. Thank you very much!
 
Upvote 0
You must log in or register to reply here.

Similar threads

willow1985
  • Solved
VBA Run-time error 91 - Object variable or With block variable not set when trying to refresh data/Sleep
Replies
4
Views
309
willow1985
willow1985
willow1985
  • Solved
VBA Compile error - Trying to send an email using VBA/Excel
Replies
6
Views
1K
gorgkhan
G
S
  • Solved
Macro to filter each column for cells that appear empty, clear the contents and move to the next column for entire table
Replies
3
Views
547
sspatriots
S
S
  • Solved
Trying to copy another column from one table to another.
Replies
2
Views
580
Alex Blakenburg
A
S
  • Solved
Run-time error '1004': No cells were found.
Replies
4
Views
1K
sspatriots
S

Forum statistics

Threads
1,224,259
Messages
6,177,480
Members
452,782
Latest member
ZCapitao

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top