Delete unused rows / clear unused rows
 

Hi Experts!

I am in the process of making a profile book for a work function. I have employees from various levels that will be attending the function. What i am trying to do is have a profile book that they will fill, then have a command button that they can click to delete all fields that do not apply to them. I have some merged cells and I have been struggling with clearing them. I have been able to partially get a delete function to work, however it is deleted almost everything!!! Below is the code I have been working with. Please advise.

I also am struggling on how to make it so the button is visible however will not print once this goes into production.

Private Sub CommandButton2_Click()
Dim nr As Long, i As Long
Application.ScreenUpdating = False
Application.EnableEvents = False 'usually want to disable, but sometimes not
nr = Range("g5:g14").SpecialCells(xlLastCell).Row
For i = nr To 1 Step -1
With Range("g5:g14").Cells(i)
If .Value = "" Then
.EntireRow.Delete
End If
End With
Next i
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub

Delete unused rows / clear unused rows
 

Try...


Private Sub CommandButton2_Click()
Dim n&
With Application
.ScreenUpdating = False
.EnableEvents = False '//optional
End With

With Range("g5:g14")
For n = 10 To 1 Step -1
If .Cells(n) = "" Then .Rows(n).Delete
Next 'n
End With

With Application
.EnableEvents = True
.ScreenUpdating = True
End With
End Sub

...so your loop only acts on the target range!

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

This partially did what I needed. I have to click the button mulitpul times to delete the values, and it is only taking out the one cell, i really need it to clear the whole row across and do them all at once to eliminate confusion. all while keeping the spacing of the sheet exactly the same and without the button moving towards the center of my document.

Hope this helps

Delete unused rows / clear unused rows
 

This partially did what I needed. I have to click the button
mulitpul
times to delete the values, and it is only taking out the one cell, i
really need it to clear the whole row across and do them all at once
to
eliminate confusion. all while keeping the spacing of the sheet
exactly
the same and without the button moving towards the center of my
document.

Hope this helps

Ah.., yes indeed! Sorry about that! Change...

.Rows(n).Delete

...to

.Cells(n).EntireRow.Delete

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

Yes very simple!

One silly question. When the rows deleted it shifted everything up so here is what I did. I changed it to .ClearContents however i have some tables below I need the cells merged, however that command will not work with the cells being merged below my specified looped range. Any ideas how i can get around this? I'm so very very close, however i cannot figure out that peice.

I may just turn the area i need into a text box if there is no other way.

Again, thank you for your help. You have been fantastic!

-- Chris

Delete unused rows / clear unused rows
 

One silly question. When the rows deleted it shifted everything up
so
here is what I did. I changed it to .ClearContents however i have
some
tables below I need the cells merged, however that command will not
work
with the cells being merged below my specified looped range. Any
ideas
how i can get around this? I'm so very very close, however i cannot
figure out that peice.

Why are the cells merged? Can Wrap Text or Center Across be used so
there's no merged cells?

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

I merged the cells to make it easy to view for the user, along with i placed shapes around the merged cells with color to make call outs and talk boxes for this profile book. Trying to make it user friently and very clear. Will go out to 300+ people, so the less complicated the better.

I just am now learning how merged cells can create a pain!

Delete unused rows / clear unused rows
 

I merged the cells to make it easy to view for the user, along with i
placed shapes around the merged cells with color to make call outs
and
talk boxes for this profile book. Trying to make it user friently
and
very clear. Will go out to 300+ people, so the less complicated the
better.

I just am now learning how merged cells can create a pain!

Exactly my point!!!

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion