.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

.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

.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

.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

.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

.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

.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

.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

.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

.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