Home > Blockchain >  Having trouble with isEmpty in visual Basic
Having trouble with isEmpty in visual Basic

Time:10-21

I have a simple script to print all rows of input into a formatted output. I would like to skip any row where column A is empty, but still print if there is data, so row 1 skip, row 2 print, row 3 print, row 4 skip, etc, based on column A. How can I do that? I have tried numerous codes with no results. Here is my current working code which prints all.

    Sub PrintAllRecords()

    RowCount = Worksheets("Input").Cells(Rows.Count, 1).End(xlUp).Row - 1

    Worksheets("Print-Auto (2)").Select
    For i = 1 To RowCount
    Range("B1").Value = i
    ActiveSheet.PrintOut Copies:=1
    Next i
    End Sub

CodePudding user response:

This should work:

Sub PrintAllRecords()

Dim inputSheet As Worksheet: Set inputSheet = ThisWorkbook.Worksheets("input")

RowCount = inputSheet.Cells(Rows.Count, 1).End(xlUp).Row - 1

Worksheets("Print-Auto (2)").Select
For i = 1 To RowCount
    If Not IsEmpty(inputSheet.Cells(i, 1)) Then
        Range("B1").Value = i
        ActiveSheet.PrintOut Copies:=1
    End If
Next i

End Sub

CodePudding user response:

Print Non-Blanks Using AutoFilter

  • This will print the rows of the used range where the first column is not blank.
  • Adjust the values in the constants section.
Option Explicit

Sub PrintNonBlanks()
    
    Const dFolderPath As String = "C:\Test\"
    Const dFileName As String = "Test.pdf"
    Const wsName As String = "Sheet1"
    Const CriteriaColumnNumber As Long = 1
    Const CriteriaString As String = "<>" ' non-blanks
    
    Dim wb As Workbook: Set wb = ThisWorkbook ' workbook containinig this code
    
    ' Create a reference to the worksheet.
    Dim ws As Worksheet: Set ws = wb.Worksheets(wsName)
    
    ' Remove any previous filter.
    If ws.AutoFilterMode Then ws.AutoFilterMode = False
    
    ' Create a reference to the worksheets used range.
    Dim urg As Range: Set urg = ws.UsedRange
    
    ' Filter first column by non-blanks.
    urg.AutoFilter CriteriaColumnNumber, CriteriaString
    
    ' Test with either...
    urg.ExportAsFixedFormat xlTypePDF, dFolderPath & dFileName
    ' ... or...
    'ws.ExportAsFixedFormat xlTypePDF, dFolderPath & dFileName
    
    ' ... before printing...
    'urg.PrintOut
    ' or...
    'ws.PrintOut
    
    ' Remove the filter.
    ws.AutoFilterMode = False
    
    ' Locate the file (folder) in Windows File Explorer.
    'wb.FollowHyperlink dFolderPath
    
End Sub

CodePudding user response:

Try this one

Sub PrintAllRecords()

RowCount = Worksheets("Input").Cells(Rows.Count, 1).End(xlUp).Row - 1

Worksheets("Print-Auto (2)").Select
For i = 1 To RowCount
   If Not IsEmpty(Worksheets("Input").Range("A" & i 1)) Then
      msgbox i
      Range("B1").Value = i
      ActiveSheet.PrintOut Copies:=1
   End If
Next i
End Sub
  • Related