Run Self made Macro through Report Property

[chintan]

I have VBA macro
It is possible me to run it from excel
But I wanted to run my macro through
//symbole//Reports\Components for Reports\Addins\SP3DReportMacros.xla




Sub DeleteBlankRows2()
Dim lastrow As Long
Dim i As Long
lastrow = Range("L:L").SpecialCells(xlLastCell).Row
Range("L10").Select
i = 1
Do While i < lastrow
If ActiveCell = "" Then
ActiveCell.EntireRow.Delete shift:=xlUp
ActiveCell.Offset(-1, 0).Select
End If
ActiveCell.Offset(1, 0).Select
i = i + 1
Loop
End Sub


I saved this macro in that .xla file but its not working (see snapshot)

Any suggestion:)


Reason why i wanted to do this:

I Wanted to delete all my macro after running 1 time .
Before saving report to another destination there should not be any VBA Remain to run again in my excel.

[dave]

Sub DeleteBlankRows2()

Dim lastrow As Long
Dim i As Long

lastrow = Range("L:L").SpecialCells(xlLastCell).Row

Range("L10").Select

i = 1

For i = lastrow To 10 Step -1

If ActiveCell = "" Then

ActiveCell.EntireRow.Delete shift:=xlUp
ActiveCell.Offset(-1, 0).Select

End If

Next x

End Sub
Chintan, I modified your code a little. When deleting rows, it's best to go from
the bottom up. When you go from the top down, when you delete, it will skip a row.
So like, let's say that row 3 and 4 need to be deleted. When your routine deletes
row 3, row 4 will shift up and become row 3 but the routine will go to the "new" row 4
thus leaving the row that you wanted to delete. Understand?

Anyway, I don't use SP3D just thought I would suggest a different way of doing it. :)

Cheers

[tim]

You need to specify sheet object to be processed.
But if it's SQL report, why don't you exclude such records by adding SQL condition?




Public Sub Test()
Dim oSheet As Worksheet

Set oSheet = Application.Worksheets("Sheet1")

( write your code with oSheet.xxx)

Set oSheet = Nothing
End Sub

[chintan]

hi tim
thanks for this i have created macro which always run in sheet 1 only through Sp3d report property
if i put sheet4 then it will show error

what this mean i didnt get?(sorry for this)
But if it's SQL report, why don't you exclude such records by adding SQL condition?
AS far as my understanding your question answer is:
the report which i generate is along with null value( in some column) and some column have result so i wanted to delete that unnessarey null value which created in my report ,Thats the reason macro this generate.
And In my condition answer from SQL QUERRY is not possible to get exact result.



Code given below:


Public Sub test()
Dim oDataSheet As Worksheet

Dim lastrow As Long
Dim i As Long
On Error GoTo ErrorHandler


For Each oDataSheet In Application.Worksheets
If oDataSheet.Visible = xlSheetVisible Then

lastrow = Range("L:L").SpecialCells(xlLastCell).Row
Range("L10").Select
i = 1
Do While i < lastrow
If ActiveCell = "" Then
ActiveCell.EntireRow.Delete shift:=xlUp
ActiveCell.Offset(-1, 0).Select
End If
ActiveCell.Offset(1, 0).Select
i = i + 1
Loop

End If
Next
GoTo ShutDown
ErrorHandler:

ShutDown:
Set oDataSheet = Nothing
End Sub



Any help

[tim]

select
XXXXX
where
XXXXX
and <expression corresponding L column> is not Null

[chintan]

See snap shot i tried previously with not null but not worked

So i decided to go for Macro

can anyone help me why my macro is not work from report property

[tim]

I don't understand what do you mean by attachment. Can you post sql used in report if you don't mind?

For Macro, you have to specify oDatasheet.Range("L:L").xxx instead of Range("L:L").xxx.
Current code you posted in #4, oDataSheet object does not work at all.

[chintan]

HI TIM THANKS FOR #5

ITS WORKED


I forgoT about "OR" in SQL




select
XXXXX
where
XXXXX
and (< L column> is not Null OR <M column> is not Null )


Now working on #7 (I will post once it done)
and everyone Please ignor my masg #6

Thanks again