Automatically Removing Rows where Cells A:R are Blank

[DukeDevil]

Hello,

I have a whole bunch of data that I keep mooving between sheets.
Sometimes I forget to delete the row once I have moved it. Is there any
sort of VBA code that I can write that recognizes when cells A to R are
BLANK and then automatically deletes the row? Please let me know.

Thank you.

 

[Thulasiram]

Hello,

I think this code that I got from this group should help you.

Private Sub CommandButton1_Click()
Dim SH As Worksheet
Dim col As Range
Dim rw As Range

Set SH = ActiveSheet

For Each col In SH.UsedRange.Columns
col.Hidden = Application.CountA(col) = 0
Next col

For Each rw In SH.UsedRange.Rows
rw.Hidden = Application.CountA(rw) = 0
Next rw
End Sub

hope this answers ur question. the above code hides the empty rows and
columns.

ALL CREDIT GOES TO THE PERSON WHO ACTAULLY HELPED ME IN SIMILAR
SITUATION AS URS.

Thulasiram.

 

[DukeDevil]

Thank you! But when I tried to paste this code into my VBA sheet, it
didnt do anything? What is Private Sub CommandButton1_Click()? I dont
understand that code?

 

[Chip Pearson]

Try something like the following:


Dim RowNum As Long
RowNum = 1 ' change as required
If Application.CountA(Range(Cells(RowNum, "A"), _
Cells(RowNum, "R"))) = 0 Then
Rows(RowNum).Delete
End If



--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com

 

[DukeDevil]

Nevermind. I got the code to work for the ENTIRE row....Now I need it
to work only if cells A thru R in the row are blank. Any ideas?

Private Sub Worksheet_Change(ByVal Target As Range)

Dim SH As Worksheet
Dim col As Range
Dim rw As Range


Set SH = ActiveSheet


For Each col In SH.UsedRange.Columns
col.Hidden = Application.CountA(col) = 0
Next col


For Each rw In SH.UsedRange.Rows
rw.Hidden = Application.CountA(rw) = 0
Next rw
End Sub

 

[Thulasiram]

I dont find any rows or columns that hide using this code. I pasted the
given code in a separate command button like this. i had column E that
was empty

Please help

 

[Tom Ogilvy]

That is because it works on rows like the OP Asked.

 

[Tom Ogilvy]

Private Sub Worksheet_Change(ByVal Target As Range)

Dim SH As Worksheet
Dim col As Range
Dim rw As Range
Set SH = ActiveSheet
For Each rw In SH.UsedRange.Rows
rw.Hidden = Application.CountA( _
sh.cells(rw.row,1).Resize(1,18)) = 0
Next rw
End Sub

 

[DukeDevil]

Thank you for your help. One last question. On the following code, how
do I get it to apply to rows 1-3000? Right now it is only referring to
rownum 1.

Private Sub Worksheet_Change(ByVal Target As Range)

Dim RowNum As Long
RowNum = 1 ' change as required
If Application.CountA(Range(Cells(RowNum, "A"), _
Cells(RowNum, "R"))) = 0 Then
Rows(RowNum).Delete
End If
End Sub

 

[Tom Ogilvy]

I gave you code that I felt worked, but you didn't use it.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim RowNum As Long
for each cell in Target.Columns(1).Cells
RowNum = cell.row
If Application.CountA(Range(Cells(RowNum, "A"), _
Cells(RowNum, "R"))) = 0 Then
Rows(RowNum).Delete
End If
next
End Sub

Would probably answer your question, but I don't know if that is what you
want.