Automatically Removing Rows where Cells A:R are Blank
 

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.

Automatically Removing Rows where Cells A:R are Blank
 

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 wrote:
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.

Automatically Removing Rows where Cells A:R are Blank
 

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?



Thulasiram wrote:
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 wrote:
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.

Automatically Removing Rows where Cells A:R are Blank
 

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" wrote in message
oups.com...
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.

Automatically Removing Rows where Cells A:R are Blank
 

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 wrote:
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 wrote:
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.

Automatically Removing Rows where Cells A:R are Blank
 

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

Chip Pearson wrote:
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" wrote in message
oups.com...
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.

Automatically Removing Rows where Cells A:R are Blank
 

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

--
Regards,
Tom Ogilvy

"Thulasiram" wrote in message
ups.com...
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

Chip Pearson wrote:
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" wrote in message
oups.com...
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.

Automatically Removing Rows where Cells A:R are Blank
 

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

--
Regards,
Tom Ogilvy


"DukeDevil" wrote in message
ups.com...
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 wrote:
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 wrote:
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.

Automatically Removing Rows where Cells A:R are Blank
 

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 wrote:
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

--
Regards,
Tom Ogilvy


"DukeDevil" wrote in message
ups.com...
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 wrote:
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 wrote:
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.

Automatically Removing Rows where Cells A:R are Blank
 

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.

--
Regards,
Tom Ogilvy


"DukeDevil" wrote in message
oups.com...
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 wrote:
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

--
Regards,
Tom Ogilvy


"DukeDevil" wrote in message
ups.com...
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 wrote:
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 wrote:
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.