Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to delete rows of referenced cells...
HI
(microsoft.public.excel.programming) I am working on a excel sheet that works as per below pattern. I have two sheets namely LI and Looks_Like. I have 10 cells in Looks_llike sheet that are referenced to 10 cells from LI sheet. Which means on Populating the cell A1 in LI sheet cell A1 in Looks_like sheet gets populated with the same value. now my question is, Now if i have only 5 cells populated in LI sheet and the other 5 are empty, how do i delete the rows in which the blank cells of Look_like sheet are present that are referenced to these empty cells in LI. Can it be done using VB code If yes... I will be gr8ful if you can help mew with the code. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to delete rows of referenced cells...
Here is one possibility.
sub DeleteRows() Dim cnt as Long cnt = application.CountA(worksheets("Li").Range("A1:A10" )) if cnt < 10 then with worksheets("Looks_Like") set rng = .Range(.Cells(cnt + 1,1),"A10").entireRow.Delete end With end sub the above assumes the filled cells will be contiguous. The next does not assume this: sub ABC() Dim rw as Long, i as Long rw = 10 for i = 10 to 1 step -1 if isempty(worksheets("LI").Cells(i,1)) then worksheets("Looks_Like").rows(i).Delete end if Next End sub -- Regards, Tom Ogilvy " wrote: HI (microsoft.public.excel.programming) I am working on a excel sheet that works as per below pattern. I have two sheets namely LI and Looks_Like. I have 10 cells in Looks_llike sheet that are referenced to 10 cells from LI sheet. Which means on Populating the cell A1 in LI sheet cell A1 in Looks_like sheet gets populated with the same value. now my question is, Now if i have only 5 cells populated in LI sheet and the other 5 are empty, how do i delete the rows in which the blank cells of Look_like sheet are present that are referenced to these empty cells in LI. Can it be done using VB code If yes... I will be gr8ful if you can help mew with the code. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to delete rows of referenced cells...
Hi Tom,
Thanks a lot for showing intrest. I did try your codes and i had the rows deleted but not exactly as i wanted. If you will be kind enough to give me ur personal mail id so that i will mail you the excel sheet, which will help you get a better picture as to what i want. Kindly reply soon. Regards, Ram On Feb 27, 7:50 pm, Tom Ogilvy wrote: Here is one possibility. sub DeleteRows() Dim cnt as Long cnt = application.CountA(worksheets("Li").Range("A1:A10" )) if cnt < 10 then with worksheets("Looks_Like") set rng = .Range(.Cells(cnt + 1,1),"A10").entireRow.Delete end With end sub the above assumes the filled cells will be contiguous. The next does not assume this: sub ABC() Dim rw as Long, i as Long rw = 10 for i = 10 to 1 step -1 if isempty(worksheets("LI").Cells(i,1)) then worksheets("Looks_Like").rows(i).Delete end if Next End sub -- Regards, Tom Ogilvy " wrote: HI (microsoft.public.excel.programming) I am working on a excel sheet that works as per below pattern. I have two sheets namely LI and Looks_Like. I have 10 cells in Looks_llike sheet that are referenced to 10 cells from LI sheet. Which means on Populating the cell A1 in LI sheet cell A1 in Looks_like sheet gets populated with the same value. now my question is, Now if i have only 5 cells populated in LI sheet and the other 5 are empty, how do i delete the rows in which the blank cells of Look_like sheet are present that are referenced to these empty cells in LI. Can it be done using VB code If yes... I will be gr8ful if you can help mew with the code.- Hide quoted text - - Show quoted text - |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to delete rows of referenced cells...
On Feb 28, 12:52 am, wrote:
Hi Tom, Thanks a lot for showing intrest. I did try your codes and i had the rows deleted but not exactly as i wanted. If you will be kind enough to give me ur personal mail id so that i will mail you the excel sheet, which will help you get a better picture as to what i want. Kindly reply soon. Regards, Ram On Feb 27, 7:50 pm, Tom Ogilvy wrote: Here is one possibility. sub DeleteRows() Dim cnt as Long cnt = application.CountA(worksheets("Li").Range("A1:A10" )) if cnt < 10 then with worksheets("Looks_Like") set rng = .Range(.Cells(cnt + 1,1),"A10").entireRow.Delete end With end sub the above assumes the filled cells will be contiguous. The next does not assume this: sub ABC() Dim rw as Long, i as Long rw = 10 for i = 10 to 1 step -1 if isempty(worksheets("LI").Cells(i,1)) then worksheets("Looks_Like").rows(i).Delete end if Next End sub -- Regards, Tom Ogilvy " wrote: HI (microsoft.public.excel.programming) I am working on a excel sheet that works as per below pattern. I have two sheets namely LI and Looks_Like. I have 10 cells in Looks_llike sheet that are referenced to 10 cells from LI sheet. Which means on Populating the cell A1 in LI sheet cell A1 in Looks_like sheet gets populated with the same value. now my question is, Now if i have only 5 cells populated in LI sheet and the other 5 are empty, how do i delete the rows in which the blank cells of Look_like sheet are present that are referenced to these empty cells in LI. Can it be done using VB code If yes... I will be gr8ful if you can help mew with the code.- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - The code works in deleting the blank rows, which includes the ones i do not require deletion. The idea here is to have a tree like structure, and it looks something like this.. Name of Book Shelf number Section 1 first row second row third row fourth row 0 0 0 0 0 0 Section 2 seventh row third row tenth row 0 0 0 0 0 0 0 Section 3 0 0 0 0 The code that was provided by you deleted the rows that had Values Name of Book, Shelf No, Section 1. I want the code to delete only the 0's i.e blank cells in LI sheet to which these cells in Looks_Like are referenced. And since under section 3 there is no entry in LI sheet, in this case the code should automatically delete section3 heading from Looks_Like sheet. Is this Possible. Regards R |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to delete rows of referenced cells...
On Feb 27, 7:50 pm, Tom Ogilvy
wrote: Here is one possibility. sub DeleteRows() Dim cnt as Long cnt = application.CountA(worksheets("Li").Range("A1:A10" )) if cnt < 10 then with worksheets("Looks_Like") set rng = .Range(.Cells(cnt + 1,1),"A10").entireRow.Delete end With end sub the above assumes the filled cells will be contiguous. The next does not assume this: sub ABC() Dim rw as Long, i as Long rw = 10 for i = 10 to 1 step -1 if isempty(worksheets("LI").Cells(i,1)) then worksheets("Looks_Like").rows(i).Delete end if Next End sub -- Regards, Tom Ogilvy " wrote: HI (microsoft.public.excel.programming) I am working on a excel sheet that works as per below pattern. I have two sheets namely LI and Looks_Like. I have 10 cells in Looks_llike sheet that are referenced to 10 cells from LI sheet. Which means on Populating the cell A1 in LI sheet cell A1 in Looks_like sheet gets populated with the same value. now my question is, Now if i have only 5 cells populated in LI sheet and the other 5 are empty, how do i delete the rows in which the blank cells of Look_like sheet are present that are referenced to these empty cells in LI. Can it be done using VB code If yes... I will be gr8ful if you can help mew with the code.- Hide quoted text - - Show quoted text - Hi I'm back again. I have decided to use the treeview functionality for the above pattern to work can you please help me with the code to Populate the tree view. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Allow Delete cells but not rows | Excel Worksheet Functions | |||
To delete the rows if certain cells contain the following | Excel Programming | |||
Delete Rows where all cells = 0 | Excel Programming | |||
Delete Rows where all cells = 0 | Excel Programming | |||
How to delete sheet that is referenced. | Excel Programming |