Determining Empty Lines And Empty Columns In Excel

[HLEE1167]

I need to write a piece of code that may determine how many empty lines and how many empty columns I do have from a given cell. Any idea on how to accomplish this?


Thank you

 

[rmikesmith]

Can you be a bit more specific and perhaps give an example or two of what you're looking to achieve.


Regards,
Mike

 

[xlbo]

from a given cell to where ??
to the vottom of the sheet ??

EmptyRows = 65536 - cell.row

or do you just want to know where the last populated cell in the spreadsheet is ??? If so - there are 2 FAQs written on this subject - have a look there

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[CautionMP]

Pretty general question, so here is a general answer with all the relevent parts.

Public Function ReturnEmptyCells(AnyCellInRange As Range) As String
Dim MyRange As Range
Dim MyRow As Range, MyColumn As Range
Dim lngBlankRow As Long, lngBlankColumn As Long
Dim lngCounter As Long
Set MyRange = Worksheets(AnyCellInRange.Worksheet.Name).UsedRange
'Find blank rows
For Each MyRow In MyRange.Rows
  lngCounter = 0
  For Each MyColumn In MyRow.Columns
    If MyColumn.Value = "" Then
      lngCounter = lngCounter + 1
    End If
  Next MyColumn
  If lngCounter = MyRow.Columns.Count Then
    lngBlankRow = lngBlankRow + 1
  End If
Next MyRow
'Find blank columns
For Each MyColumn In MyRange.Columns
  lngCounter = 0
  For Each MyRow In MyColumn.Rows
    If MyRow.Value = "" Then
      lngCounter = lngCounter + 1
    End If
  Next MyRow
  If lngCounter = MyColumn.Rows.Count Then
    lngBlankColumn = lngBlankColumn + 1
  End If
Next MyColumn
Set MyRow = Nothing
Set MyColumn = Nothing
Set MyRange = Nothing
ReturnEmptyCells = "Blank Rows: " & lngBlankRow & "," & "Blank Columns: " & lngBlankColumn
End Function

Hope this helps,
CMP

(GMT-07:00) Mountain Time (US & Canada)