Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
.SpecialCells(xlCellTypeBlanks).Delete without going to the sheet
The whole macro is below, which does what I want. It is in a standard module and will be run from any of about 8 to 10 different worksheets.
This snippet deleting blank cells in a range ends up leaving me with the "Project Priorities" sheet as the active sheet. Sheets("Project Priorities").Activate Range(Cells(2, blnkCol - 2), Cells(blnkRow, blnkCol)) _ .SpecialCells(xlCellTypeBlanks).Delete (xlShiftUp) How would I return to the original sheet (or never leave it) that I run the code on? I know I should be able to do work on cells on other sheet with no need to select the sheet, but my head is not getting around how to do it if the activesheet can be a different each time the code is executed. Thanks, Howard Sub Delete_Task_Proj_Proir() Dim tskCel As Range Dim tskCol As Long Dim TheTaskRng As Range ' input box selections Dim aTsk As Range, aTskDel As Range, blkCel As Range Dim blnkRow As Long, blnkCol As Long On Error GoTo NotValidInput Set TheTaskRng = Application.InputBox( _ Prompt:="Select green font COMPLETED Task/s in Column E" & vbCr & _ "For removal from ""Project Priorities"" sheet", Type:=8) If Not TheTaskRng.Column = 5 Then MsgBox "Column E cell selection only" Exit Sub End If Application.ScreenUpdating = False For Each aTsk In TheTaskRng Set aTskDel = Sheets("Project Priorities").Cells.Find(What:=aTsk, _ LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, _ SearchDirection:=xlNext, MatchCase:=False) blnkRow = aTskDel.Row blnkCol = aTskDel.Column If Not aTskDel Is Nothing Then aTskDel.Offset(, -2).Resize(1, 3).ClearContents aTsk.Offset(, 3) = aTsk.Offset(, 3) & "PP Del" End If Sheets("Project Priorities").Activate Range(Cells(2, blnkCol - 2), Cells(blnkRow, blnkCol)) _ .SpecialCells(xlCellTypeBlanks).Delete (xlShiftUp) Next 'aTsk NotValidInput: Application.ScreenUpdating = True End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
.SpecialCells(xlCellTypeBlanks).Delete without going to the sheet
A couple of pointers...
TheTaskRng.Parent is a fully qualified ref to the sheet the selected cells are on. With Sheets("Project Priorities") .Range(.Cells(2, blnkCol - 2), .Cells(blnkRow, blnkCol)).SpecialCells(xlCellTypeBlanks).Delete (xlShiftUp) End With ...is a fully qualified ref that does not require the sheet be the active sheet. HTH -- 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 email has been checked for viruses by Avast antivirus software. https://www.avast.com/antivirus |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
.SpecialCells(xlCellTypeBlanks).Delete without going to the sheet
hi L. Howard,
Sheets("Project Priorities").Range(Cells(2, blnkCol - 2), Cells(blnkRow, blnkCol)) _ .SpecialCells(xlCellTypeBlanks).Delete (xlShiftUp) isabelle Le 2016-06-17 Ã* 00:40, L. Howard a écrit : The whole macro is below, which does what I want. It is in a standard module and will be run from any of about 8 to 10 different worksheets. This snippet deleting blank cells in a range ends up leaving me with the "Project Priorities" sheet as the active sheet. Sheets("Project Priorities").Activate Range(Cells(2, blnkCol - 2), Cells(blnkRow, blnkCol)) _ .SpecialCells(xlCellTypeBlanks).Delete (xlShiftUp) How would I return to the original sheet (or never leave it) that I run the code on? I know I should be able to do work on cells on other sheet with no need to select the sheet, but my head is not getting around how to do it if the activesheet can be a different each time the code is executed. Thanks, Howard |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
.SpecialCells(xlCellTypeBlanks).Delete without going to the sheet
On Thursday, June 16, 2016 at 10:18:16 PM UTC-7, GS wrote:
A couple of pointers... TheTaskRng.Parent is a fully qualified ref to the sheet the selected cells are on. With Sheets("Project Priorities") .Range(.Cells(2, blnkCol - 2), .Cells(blnkRow, blnkCol)).SpecialCells(xlCellTypeBlanks).Delete (xlShiftUp) End With ..is a fully qualified ref that does not require the sheet be the active sheet. HTH -- Garry I'll give the With statement another try. I went there to start but did not work for me. Maybe I had something else out of kilter. And as I look at your With right now, I see now I was omitting the .Range (dot). DUH!! Thanks Garry |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
.SpecialCells(xlCellTypeBlanks).Delete without going to the sheet
On Thursday, June 16, 2016 at 10:28:45 PM UTC-7, isabelle wrote:
hi L. Howard, Sheets("Project Priorities").Range(Cells(2, blnkCol - 2), Cells(blnkRow, blnkCol)) _ .SpecialCells(xlCellTypeBlanks).Delete (xlShiftUp) isabelle Le 2016-06-17 Ã* 00:40, L. Howard a écrit : The whole macro is below, which does what I want. It is in a standard module and will be run from any of about 8 to 10 different worksheets. This snippet deleting blank cells in a range ends up leaving me with the "Project Priorities" sheet as the active sheet. Sheets("Project Priorities").Activate Range(Cells(2, blnkCol - 2), Cells(blnkRow, blnkCol)) _ .SpecialCells(xlCellTypeBlanks).Delete (xlShiftUp) Hi isabelle, I had tried like you posted here, but was missing the . (Dot) in that attempt also. Thanks Howard |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
.SpecialCells(xlCellTypeBlanks).Delete without going to the sheet
Hi isabelle, I had tried like you posted here, but was missing the . (Dot) in that attempt also. Thanks Howard Hi isabelle I misspoke, I retried your suggestion omitting the dot, and got a compile error, so now I don't know were I was going wrong. But it be fixed now, thanks again. Howard |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
.SpecialCells(xlCellTypeBlanks).Delete without going to the sheet
Le 2016-06-17 Ã* 02:43, L. Howard a écrit :
Hi isabelle I misspoke, I retried your suggestion omitting the dot, and got a compile error, so now I don't know were I was going wrong. But it be fixed now, thanks again. Howard i'm glad that was able to help you isabelle |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
.SpecialCells(xlCellTypeBlanks).Delete without going to the sheet
On Thursday, June 16, 2016 at 10:18:16 PM UTC-7, GS wrote:
A couple of pointers... TheTaskRng.Parent is a fully qualified ref to the sheet the selected cells are on. With Sheets("Project Priorities") .Range(.Cells(2, blnkCol - 2), .Cells(blnkRow, blnkCol)).SpecialCells(xlCellTypeBlanks).Delete (xlShiftUp) End With ..is a fully qualified ref that does not require the sheet be the active sheet. HTH -- Garry I'll give the With statement another try. I went there to start but did not work for me. Maybe I had something else out of kilter. And as I look at your With right now, I see now I was omitting the .Range (dot). DUH!! Thanks Garry Glad to help! What's important about programming VBA is that you always use *fully qualified object refs* in code. This is way more efficient approach than having to '.Select' or '.Activate' things. (Note, though, that some actions require objects be selected/activated!) -- 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 email has been checked for viruses by Avast antivirus software. https://www.avast.com/antivirus |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
.SpecialCells(xlCellTypeBlanks).Delete without going to the sheet
Hi Howard,
Am Thu, 16 Jun 2016 21:40:32 -0700 (PDT) schrieb L. Howard: Sheets("Project Priorities").Activate Range(Cells(2, blnkCol - 2), Cells(blnkRow, blnkCol)) _ .SpecialCells(xlCellTypeBlanks).Delete (xlShiftUp) another suggestion without ClearContents and deleting the blank cells into the loop: Sub Test() Dim TheTaskRng As Range ' input box selections Dim aTsk As Range, aTskDel As Range, rngBig As Range On Error GoTo NotValidInput Set TheTaskRng = Application.InputBox( _ Prompt:="Select green font COMPLETED Task/s in Column E" & vbCr & _ "For removal from ""Project Priorities"" sheet", Type:=8) If Not TheTaskRng.Column = 5 Then MsgBox "Column E cell selection only" Exit Sub End If Application.ScreenUpdating = False With Sheets("Project Priorities") For Each aTsk In TheTaskRng Set aTskDel = .Cells.Find(What:=aTsk, LookIn:=xlValues, LookAt:=xlPart) If Not aTskDel Is Nothing Then aTsk.Offset(, 3) = aTsk.Offset(, 3) & "PP Del" If rngBig Is Nothing Then Set rngBig = aTskDel.Offset(, -2).Resize(1, 3) Else Set rngBig = Union(rngBig, aTskDel.Offset(, -2).Resize(1, 3)) End If End If Next 'aTsk rngBig.Delete shift:=xlUp End With NotValidInput: Application.ScreenUpdating = True End Sub Regards Claus B. -- Windows10 Office 2016 |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
.SpecialCells(xlCellTypeBlanks).Delete without going to the sheet
On Friday, June 17, 2016 at 4:32:32 AM UTC-7, Claus Busch wrote:
Hi Howard, Am Thu, 16 Jun 2016 21:40:32 -0700 (PDT) schrieb L. Howard: Sheets("Project Priorities").Activate Range(Cells(2, blnkCol - 2), Cells(blnkRow, blnkCol)) _ .SpecialCells(xlCellTypeBlanks).Delete (xlShiftUp) another suggestion without ClearContents and deleting the blank cells into the loop: Sub Test() Dim TheTaskRng As Range ' input box selections Dim aTsk As Range, aTskDel As Range, rngBig As Range On Error GoTo NotValidInput Set TheTaskRng = Application.InputBox( _ Prompt:="Select green font COMPLETED Task/s in Column E" & vbCr & _ "For removal from ""Project Priorities"" sheet", Type:=8) If Not TheTaskRng.Column = 5 Then MsgBox "Column E cell selection only" Exit Sub End If Application.ScreenUpdating = False With Sheets("Project Priorities") For Each aTsk In TheTaskRng Set aTskDel = .Cells.Find(What:=aTsk, LookIn:=xlValues, LookAt:=xlPart) If Not aTskDel Is Nothing Then aTsk.Offset(, 3) = aTsk.Offset(, 3) & "PP Del" If rngBig Is Nothing Then Set rngBig = aTskDel.Offset(, -2).Resize(1, 3) Else Set rngBig = Union(rngBig, aTskDel.Offset(, -2).Resize(1, 3)) End If End If Next 'aTsk rngBig.Delete shift:=xlUp End With NotValidInput: Application.ScreenUpdating = True End Sub Regards Claus B. -- Hi Claus, That indeed does the trick. Not apparent to me how it is getting the job done, but it is. Will need to study that for sure. Thanks, Howard |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
XL2002: Selection.SpecialCells(xlCellTypeBlanks).Select | Excel Programming | |||
Work around to SpecialCells(xlCellTypeBlanks)... | Excel Discussion (Misc queries) | |||
.SpecialCells(xlCellTypeBlanks).EntireRow.Delete | Excel Programming | |||
Check SpecialCells(xlCellTypeBlanks) for 0 blanks | Excel Programming | |||
specialcells(xlcelltypeblanks) | Excel Programming |