Checking each cell in a row

[hockeyfan]

So I have a matrix a1:z250. Column A is names and b-z is data. If there is a name on the list that doesn't not have any data (i.e. columns b-z are blank), I wanna delete this row. I know how to do this with loops, but is there an easier way?

[stanleydgrom]

hockeyfan,

Please post your workbook (or a sample if the data is sensative) - scroll down and see "Manage Attachments".

[hockeyfan]

There is no workbook for this. It was more of a general question.

[RolfJ]

You could select the data range and then use Data | Filter | Autofilter from the main menu. Then select (Blanks) for all of the columns past column A. This will display only those names in column A that don't have any data. Now you can select those rows and delete them.

[GTO]

Hi there,

I don't know that its any 'easier', but you could try:


Sub exa()
Dim MyRange As Range

With Sheet1
Set MyRange = .Range("B1:Z250")
With .Range("AA1:AA250")
.Formula = "=IF(COUNTA(B1:Z1)=0,1,"""")"
.Value = .Value
.SpecialCells(xlCellTypeConstants).EntireRow.Delete
End With
End With
End Sub


Hope that helps,

Mark

[stanleydgrom]

hockeyfan,

Try:


Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

Adding the Macro
1. Copy the below macro, by highlighting the macro code and pressing the keys CTRL + C
2. Open your workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Paste the code by pressing the keys CTRL + V
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel, open the workbook, and press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.





Option Explicit
Sub hockeyfan()
Dim LR As Long, a As Long, rng As Range, NbrOf As Long
Application.ScreenUpdating = False
LR = Cells(Rows.Count, 1).End(xlUp).Row
For a = LR To 1 Step -1
Set rng = Range("B" & a & ":Z" & a)
NbrOf = WorksheetFunction.CountBlank(rng)
If NbrOf = 25 Then Rows(a).EntireRow.Delete
Next a
Application.ScreenUpdating = True
End Sub




Then run the "hockeyfan" macro.

[mikerickson]

Try this
With Range("AA:AA").Resize(Range("A65536").End(xlup).Row,1)
.FormulaR1C1 = 1/COUNTA(RC2:RC[-1])
.SpecialCells(xlCellTypeFormulas,xlErrors).EntireRow.Delete
End With

[mdmackillop]

Hi Mike,
You're missing the quotes around the formula, and I've added a ClearContents line to tidy up.
Regards
Malcolm



With Range("AA:AA").Resize(Range("A65536").End(xlUp).Row, 1)
.FormulaR1C1 = "=1/COUNTA(RC2:RC[-1])"
.SpecialCells(xlCellTypeFormulas, xlErrors).EntireRow.Delete
.ClearContents
End With

[mikerickson]

Thanks for catching the goofs.