0

I am trying to write an IsEmpty code to check if there are any empty cells in a certain selection range. The thing is it cannot be hard-coded such as B100:J because I am importing data constantly so the data range will constantly change and never be the same. So, before I make the excel a PDF via Macro, I want to check all the cells to see if there is an empty cell, therefore I can delete it or make adjustments to it. The code below is what I have so far, but it doesn't run and I am kind of stuck, anything helps! The reason I have B15:J15 is it because that's where the data will start and end, but I will never know how long it will go down, it can go from B100:J100 or B30:J30. Thanks, everyone.

Sub IsEmpty()
'check to see if there are any blank cells before archiving

Dim N As Range
Set N = Range("B15:J15").End(xlDown)

If IsEmpty(Range(N).Value) = False Then
MsgBox ("Blank information, please double check")

End If

End Sub
Mark S.
  • 1,474
  • 1
  • 6
  • 20
Scott P
  • 29
  • 8
  • 2
    Use `Application.CountBlank` here... but you've got a few more issues than that. – BigBen Jan 15 '20 at 18:27
  • 2
    or `Range().SpecialCells(xlCellTypeBlanks).Count`. If it's greater than `0` you have blank cells – cybernetic.nomad Jan 15 '20 at 18:30
  • 2
    `but I will never know how long it will go down` Find the last row using [.Find](https://stackoverflow.com/questions/11169445/error-in-finding-last-used-cell-in-excel-with-vba/11169920#11169920) and then construct your range. After that you can use `CountBlank` as @BigBen suggested – Siddharth Rout Jan 15 '20 at 18:33
  • 2
    Also if you want to know which cell is blank then use `.Find` again for that :) – Siddharth Rout Jan 15 '20 at 18:33
  • 1
    Not a good idea to name your sub a name of a proper command – Darrell H Jan 15 '20 at 19:32

1 Answers1

0

Use below code to check last empty row. Here I am assuming you checking for column B

lastRow = "your workbook".sheets("sheet name").Cells(Rows.Count, "B").End(xlUp).Row

Shiv Patne
  • 543
  • 3
  • 10