Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Removing blank cells in rows of data | Excel Worksheet Functions | |||
Removing Blank Rows ? | New Users to Excel | |||
Removing blank rows | Excel Programming | |||
Removing blank rows | Excel Programming | |||
Copying and pasting a worksheet to a blank and removing blank rows | Excel Programming |