Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 Quote:
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
|
|||
|
|||
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 Quote:
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#7
|
|||
|
|||
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! Quote:
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
delete unused rows | Excel Programming | |||
macro to delete hidden or unused rows | Excel Programming | |||
Hiding Unused Rows | Excel Programming | |||
Delete Unused (4000) Rows | Excel Programming | |||
VB Code to Delete Unused Rows | Excel Programming |