Are all cells in the column empty

DavidPaul · #1 12-15-2024, 03:34 PM

I want code that checks whether all cells in a specified column are empty. I currently do it by looping and checking each cell individually, but this is very slow, so I'm looking for a more efficient solution.

The main problem seems to be that Column doesn't have a Range property.

Any ideas for a workaround? Thanks in advance.

macropod · #2 12-15-2024, 04:46 PM

That depends on how you're going about it. For a designated table & column, you could use something based on:

Code:

Sub CheckTableData()
Application.ScreenUpdating = False
Dim t As Long, c As Long, r As Long, l As Long, bFit As Boolean
With ActiveDocument
  If .Tables.Count = 0 Then Exit Sub
  t = InputBox("Which table?" & vbCr & "Choose from 1 to " & .Tables.Count, , 1)
  c = InputBox("Which column?" & vbCr & "Choose from 1 to " & .Tables(t).Columns.Count, , 1)
  With .Tables(t)
    bFit = .AllowAutoFit
    .AllowAutoFit = False
    For r = 1 To .Rows.Count
      l = l + Len(.Cell(r, c).Range.Text) - 2
    Next
    .AllowAutoFit = bFit
  End With
End With
MsgBox l
Application.ScreenUpdating = True
End Sub

If you know the table # and column #, you could hard-code those.

In each case, the macro returns how may text characters are in the designated cells.

DavidPaul · #3 12-16-2024, 07:22 PM

Thanks, macropod.

I haven't tried out the solution, as it involves looping through the cells, which I've found is too slow, which I'm trying to overcome.

macropod · #4 12-16-2024, 07:30 PM

So how many cells are you trying to process, and what are you trying to achieve?

DavidPaul · #5 12-16-2024, 07:46 PM

Quote:

Originally Posted by macropod

So how many cells are you trying to process, and what are you trying to achieve?

I've got a lengthy macro that does lots of formatting depending on the scenario. The only requirement is that the selection is within a table.

One of the tests I need to do (to decide how to do the formatting) is "does this column contain any text". It turns out this isn't as simple as you'd think. For a start, Column doesn't have a Range property (and therefore no Text property). It also turns out that, if you select a column and try and convert that to a range, you get the whole table, not just the column.

The reason for my post is that I'm hoping someone has struck similar problems and has found a workaround. I've searched online, and there are many "solutions", but so far they all fail. And all my attempts to "work around" have failed.

macropod · #6 12-16-2024, 08:08 PM

Well, given what you want to achieve, I don't think you'll find any non-looping solution.

On my old laptop, the looping code I posted only takes about 1 second to process a 500-row table.

DavidPaul · #7 12-17-2024, 12:54 PM

I've finally found the time to try your second solution and it works in a flash. Not sure what was causing my attempt to be so slow, but I'm very happy.

Thanks very much, Paul

macropod · #8 12-17-2024, 03:24 PM

Possibly because my code (temporarily) uses:
.AllowAutoFit = False
Tables that allow autofitting can be slow to process with VBA, especially if you're playing around with formatting.

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Calculate average in a column, but ignore empty cells?	alnonymous	Word	1	08-09-2022 07:46 PM
If a2 is not empty, color empty cells in b2:af2	turkanet	Excel	2	08-20-2017 11:00 PM
Apparently empty (blank) cells aren't empty	daymaker	Excel	3	03-08-2012 03:41 PM