How to delete rows of referenced cells...

[sreeram.i.c]

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.

 

[Guest]

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

 

[sreeram.i.c]

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

 

[sreeram.i.c]

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







- 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

 

[sreeram.i.c]

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









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