How to quickly remove blank row using VBA?

[karoc]

My project does not allow add new sub task under some task, but cannot forbid
the operation of insert new row, so I remove the blank rows before save the
project, so do have a quickly way to remove the blank rows? thanks!

 

[John]

My project does not allow add new sub task under some task, but cannot forbid
the operation of insert new row, so I remove the blank rows before save the
project, so do have a quickly way to remove the blank rows? thanks!

karoc,
What I would probably do is to create a filter that isolates all blank
task rows. Then use SelectAll followed by a Delete. I don't have Project
open at the moment so there may be a few more details, but at least this
gives you a general approach.

Hope this helps.

John
Project MVP

 

[karoc]

karoc,
What I would probably do is to create a filter that isolates all blank
task rows. Then use SelectAll followed by a Delete. I don't have Project
open at the moment so there may be a few more details, but at least this
gives you a general approach.

Hope this helps.

John
Project MVP

Hi, John

Thanks. but the blank row cannot be filtered.

 

[John]

Hi, John

Thanks. but the blank row cannot be filtered.

karoc,
Yeah, you're right. I forgot that blank lines are null objects.

OK, here's another approach. Sort by Task Name without keeping the
outline structure. Then find and select the last task with a non-null
entry in the Name field. This will require you to look for an error in a
loop that looks at all tasks in the current active view (a null task
object generates a runtime error so use the On Error Resume Next code
structure to avoid halting execution when you hit the error). When the
controlled error occurs, branch to code that selects the next row
through the end of the file (you can get that from the
ActiveProject.Tasks.Count Property). Once selected, those blank rows can
be deleted. Finally, re-sort by ID.

I don't have time to actually code this up and test it for you, but it
should work unless someone else has a better approach.

Hope this helps.

John
Project MVP

 

[karoc]

Thanks John, that is a great idea! it go much faster than before.

 

[John]

Thanks John, that is a great idea! it go much faster than before.

karoc,
You're welcome and thanks for the feedback.

John
Project MVP

 

[Joy]

Hi John

Could you please tell me how to implement:
When the
controlled error occurs, branch to code that selects the next row
through the end of the file (you can get that from the
ActiveProject.Tasks.Count Property). Once selected, those blank rows can
be deleted.


....thanks

I tried, but empty rows cannot be deleted. ..

 

[John]

Hi John

Could you please tell me how to implement:
When the
controlled error occurs, branch to code that selects the next row
through the end of the file (you can get that from the
ActiveProject.Tasks.Count Property). Once selected, those blank rows can
be deleted.


...thanks

I tried, but empty rows cannot be deleted. ..

Joy,
The basic structure of the code could be something like this:
[Sort by task name without keeping the outline structure]
[Set up a row counter to track where the loop is with respect to the
active view]
For Each t in ActiveSelection.Tasks
On Error Resume Next
If Err <> 0 Then
[select all subsequent rows of the active view using the value of
the row counter plus 1 through ActivePRoject.Tasks.Count]
EditDelete
On Error GoTo 0
End If
RowCnt = RowCnt + 1
Next t
[Sort by ID]

Yes you can delete blank rows.

John
Project MVP

 

[Joy]

got it, thank you.

how about this:

'delete blank rows
On Error Resume Next
For Each tsk In ActiveProject.Tasks
If tsk Is Nothing Then
tsk.Delete

End If
Next tsk

i guess it's slower than yours, right??

Hi John

Could you please tell me how to implement:
When the
controlled error occurs, branch to code that selects the next row
through the end of the file (you can get that from the
ActiveProject.Tasks.Count Property). Once selected, those blank rows can
be deleted.


...thanks

I tried, but empty rows cannot be deleted. ..

Joy,
The basic structure of the code could be something like this:
[Sort by task name without keeping the outline structure]
[Set up a row counter to track where the loop is with respect to the
active view]
For Each t in ActiveSelection.Tasks
On Error Resume Next
If Err <> 0 Then
[select all subsequent rows of the active view using the value of
the row counter plus 1 through ActivePRoject.Tasks.Count]
EditDelete
On Error GoTo 0
End If
RowCnt = RowCnt + 1
Next t
[Sort by ID]

Yes you can delete blank rows.

John
Project MVP

 

[John]

got it, thank you.

how about this:

'delete blank rows
On Error Resume Next
For Each tsk In ActiveProject.Tasks
If tsk Is Nothing Then
tsk.Delete

End If
Next tsk

i guess it's slower than yours, right??

Joy,
You're welcome and thanks for the feedback.

Whenever possible I try to do multiple repetitive operations as a group.
The original poster had several blank rows inserted at various spots in
his file. My suggested approach gathers all those into one group and
then does a one-time delete. Your situation may be different. If the
simpler code you developed works for you, then use it.

John
Project MVP

Hi John

Could you please tell me how to implement:
When the
controlled error occurs, branch to code that selects the next row
through the end of the file (you can get that from the
ActiveProject.Tasks.Count Property). Once selected, those blank rows can
be deleted.


...thanks

I tried, but empty rows cannot be deleted. ..

Joy,
The basic structure of the code could be something like this:
[Sort by task name without keeping the outline structure]
[Set up a row counter to track where the loop is with respect to the
active view]
For Each t in ActiveSelection.Tasks
On Error Resume Next
If Err <> 0 Then
[select all subsequent rows of the active view using the value of
the row counter plus 1 through ActivePRoject.Tasks.Count]
EditDelete
On Error GoTo 0
End If
RowCnt = RowCnt + 1
Next t
[Sort by ID]

Yes you can delete blank rows.

John
Project MVP

:

Thanks John, that is a great idea! it go much faster than before.

karoc,
You're welcome and thanks for the feedback.

John
Project MVP

:

:


My project does not allow add new sub task under some task,
but
cannot
forbid
the operation of insert new row, so I remove the blank rows
before
save
the
project, so do have a quickly way to remove the blank rows?
thanks!

karoc,
What I would probably do is to create a filter that isolates
all
blank
task rows. Then use SelectAll followed by a Delete. I don't
have
Project
open at the moment so there may be a few more details, but at
least
this
gives you a general approach.

Hope this helps.

John
Project MVP


Hi, John

Thanks. but the blank row cannot be filtered.

karoc,
Yeah, you're right. I forgot that blank lines are null objects.

OK, here's another approach. Sort by Task Name without keeping the
outline structure. Then find and select the last task with a
non-null
entry in the Name field. This will require you to look for an error
in
a
loop that looks at all tasks in the current active view (a null
task
object generates a runtime error so use the On Error Resume Next
code
structure to avoid halting execution when you hit the error). When
the
controlled error occurs, branch to code that selects the next row
through the end of the file (you can get that from the
ActiveProject.Tasks.Count Property). Once selected, those blank
rows
can
be deleted. Finally, re-sort by ID.

I don't have time to actually code this up and test it for you, but
it
should work unless someone else has a better approach.

Hope this helps.

John
Project MVP