Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Discussion (Misc queries) > 2 macro help
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 468
Default 2 macro help
Hi, i have in a macro this part of the code that delete empty rows:

Dim lRow As Long
With Application
..ScreenUpdating = False
..Calculation = xlCalculationManual
..EnableEvents = False
For lRow = Cells.Find(What:="*", After:=[A1], _
SearchDirection:=xlPrevious).Row To 1 Step -1
If WorksheetFunction.CountA(Rows(lRow)) = 0 Then Rows(lRow).Delete
Next lRow
..ScreenUpdating = True
..Calculation = xlCalculationAutomatic
..EnableEvents = True

and then this one:

range("A2:A300").Select
Selection.ClearContents
range("A2").Select

range("A2").Select
ActiveCell.FormulaR1C1 = "=IF(ISBLANK(RC[1]),"""",TODAY())"
Selection.AutoFill Destination:=range("A2:A300"), Type:=xlFillDefault
range("A2:A300").Select
range("A2").Select
The problem is that the second part is blocking the first one. i neet to
delete empty rows and then to add formula.
What am i doing wrong?
Thanks!
Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 2,722
Default 2 macro help
Delete the line near top that says:
..EnableEvents = True

At end of your macro, add the line:
Application.EnableEvents = True
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"puiuluipui" wrote:

Hi, i have in a macro this part of the code that delete empty rows:

Dim lRow As Long
With Application
.ScreenUpdating = False
.Calculation = xlCalculationManual
.EnableEvents = False
For lRow = Cells.Find(What:="*", After:=[A1], _
SearchDirection:=xlPrevious).Row To 1 Step -1
If WorksheetFunction.CountA(Rows(lRow)) = 0 Then Rows(lRow).Delete
Next lRow
.ScreenUpdating = True
.Calculation = xlCalculationAutomatic
.EnableEvents = True

and then this one:

range("A2:A300").Select
Selection.ClearContents
range("A2").Select

range("A2").Select
ActiveCell.FormulaR1C1 = "=IF(ISBLANK(RC[1]),"""",TODAY())"
Selection.AutoFill Destination:=range("A2:A300"), Type:=xlFillDefault
range("A2:A300").Select
range("A2").Select
The problem is that the second part is blocking the first one. i neet to
delete empty rows and then to add formula.
What am i doing wrong?
Thanks!
Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 9,101
Default 2 macro help
when deleting rows use a Do Loop, not a for loop. The deleting of Rows will
cause a problem with the For loop.

Dim lRow As Long
With Application
.ScreenUpdating = False
.Calculation = xlCalculationManual
.EnableEvents = False

set c = lRow = Cells.Find(What:="*", After:=[A1], _
SearchDirection:=xlPrevious)
if not c is nothing then
LastRow = c

RowCount = LastRow
Do while RowCount = 1


If WorksheetFunction.CountA(Rows(Rowcount)) = 0 Then
Rows(RowCount).Delete
end if
RowCount = RowCount - 1
Loop

.ScreenUpdating = True
.Calculation = xlCalculationAutomatic
.EnableEvents = True

end with


range("A2:A300").ClearContents
range("A2").FormulaR1C1 = "=IF(ISBLANK(RC[1]),"""",TODAY())"
range("A2").AutoFill _
Destination:=range("A2:A300"), _
Type:=xlFillDefault


"puiuluipui" wrote:

Hi, i have in a macro this part of the code that delete empty rows:

Dim lRow As Long
With Application
.ScreenUpdating = False
.Calculation = xlCalculationManual
.EnableEvents = False
For lRow = Cells.Find(What:="*", After:=[A1], _
SearchDirection:=xlPrevious).Row To 1 Step -1
If WorksheetFunction.CountA(Rows(lRow)) = 0 Then Rows(lRow).Delete
Next lRow
.ScreenUpdating = True
.Calculation = xlCalculationAutomatic
.EnableEvents = True

and then this one:

range("A2:A300").Select
Selection.ClearContents
range("A2").Select

range("A2").Select
ActiveCell.FormulaR1C1 = "=IF(ISBLANK(RC[1]),"""",TODAY())"
Selection.AutoFill Destination:=range("A2:A300"), Type:=xlFillDefault
range("A2:A300").Select
range("A2").Select
The problem is that the second part is blocking the first one. i neet to
delete empty rows and then to add formula.
What am i doing wrong?
Thanks!
Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 468
Default 2 macro help
Hi Luke, is not working. This is how you said?
The space in the middle is the place from were i removed ".EnableEvents =
True" :

Dim lRow As Long
With Application
..ScreenUpdating = False
..Calculation = xlCalculationManual
..EnableEvents = False
For lRow = Cells.Find(What:="*", After:=[A1], _
SearchDirection:=xlPrevious).Row To 1 Step -1
If WorksheetFunction.CountA(Rows(lRow)) = 0 Then Rows(lRow).Delete
Next lRow
..ScreenUpdating = True
..Calculation = xlCalculationAutomatic


range("A2:A300").Select
Selection.ClearContents
range("A2").Select
range("A2").Select
ActiveCell.FormulaR1C1 = "=IF(ISBLANK(RC[1]),"""",TODAY())"
Selection.AutoFill Destination:=range("A2:A300"), Type:=xlFillDefault
range("A2:A300").Select
range("A2").Select
Application.EnableEvents = True

Thanks!

"Luke M" wrote:

Delete the line near top that says:
.EnableEvents = True

At end of your macro, add the line:
Application.EnableEvents = True
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"puiuluipui" wrote:

Hi, i have in a macro this part of the code that delete empty rows:

Dim lRow As Long
With Application
.ScreenUpdating = False
.Calculation = xlCalculationManual
.EnableEvents = False
For lRow = Cells.Find(What:="*", After:=[A1], _
SearchDirection:=xlPrevious).Row To 1 Step -1
If WorksheetFunction.CountA(Rows(lRow)) = 0 Then Rows(lRow).Delete
Next lRow
.ScreenUpdating = True
.Calculation = xlCalculationAutomatic
.EnableEvents = True

and then this one:

range("A2:A300").Select
Selection.ClearContents
range("A2").Select

range("A2").Select
ActiveCell.FormulaR1C1 = "=IF(ISBLANK(RC[1]),"""",TODAY())"
Selection.AutoFill Destination:=range("A2:A300"), Type:=xlFillDefault
range("A2:A300").Select
range("A2").Select
The problem is that the second part is blocking the first one. i neet to
delete empty rows and then to add formula.
What am i doing wrong?
Thanks!
Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 468
Default 2 macro help
Hi Joel, i receive this error after i paste your code:
Compile error:
End With without with.

What is wrong?
Thanks!

"Joel" wrote:

when deleting rows use a Do Loop, not a for loop. The deleting of Rows will
cause a problem with the For loop.

Dim lRow As Long
With Application
.ScreenUpdating = False
.Calculation = xlCalculationManual
.EnableEvents = False

set c = lRow = Cells.Find(What:="*", After:=[A1], _
SearchDirection:=xlPrevious)
if not c is nothing then
LastRow = c

RowCount = LastRow
Do while RowCount = 1


If WorksheetFunction.CountA(Rows(Rowcount)) = 0 Then
Rows(RowCount).Delete
end if
RowCount = RowCount - 1
Loop

.ScreenUpdating = True
.Calculation = xlCalculationAutomatic
.EnableEvents = True

end with


range("A2:A300").ClearContents
range("A2").FormulaR1C1 = "=IF(ISBLANK(RC[1]),"""",TODAY())"
range("A2").AutoFill _
Destination:=range("A2:A300"), _
Type:=xlFillDefault


"puiuluipui" wrote:

Hi, i have in a macro this part of the code that delete empty rows:

Dim lRow As Long
With Application
.ScreenUpdating = False
.Calculation = xlCalculationManual
.EnableEvents = False
For lRow = Cells.Find(What:="*", After:=[A1], _
SearchDirection:=xlPrevious).Row To 1 Step -1
If WorksheetFunction.CountA(Rows(lRow)) = 0 Then Rows(lRow).Delete
Next lRow
.ScreenUpdating = True
.Calculation = xlCalculationAutomatic
.EnableEvents = True

and then this one:

range("A2:A300").Select
Selection.ClearContents
range("A2").Select

range("A2").Select
ActiveCell.FormulaR1C1 = "=IF(ISBLANK(RC[1]),"""",TODAY())"
Selection.AutoFill Destination:=range("A2:A300"), Type:=xlFillDefault
range("A2:A300").Select
range("A2").Select
The problem is that the second part is blocking the first one. i neet to
delete empty rows and then to add formula.
What am i doing wrong?
Thanks!
Reply With QuoteReply With Quote
  #6   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 5,934
Default 2 macro help
The deleting of Rows will cause a problem with the For loop.

Not if you use a For loop that loops from the end of the columns of data
upward to the beginning of the data (using a Step -1 statement to effect the
backward iteration). For example...

For X = LastRow To StartRow Step -1

--
Rick (MVP - Excel)


"Joel" wrote in message
...
when deleting rows use a Do Loop, not a for loop. The deleting of Rows
will
cause a problem with the For loop.

Dim lRow As Long
With Application
.ScreenUpdating = False
.Calculation = xlCalculationManual
.EnableEvents = False

set c = lRow = Cells.Find(What:="*", After:=[A1], _
SearchDirection:=xlPrevious)
if not c is nothing then
LastRow = c

RowCount = LastRow
Do while RowCount = 1


If WorksheetFunction.CountA(Rows(Rowcount)) = 0 Then
Rows(RowCount).Delete
end if
RowCount = RowCount - 1
Loop

.ScreenUpdating = True
.Calculation = xlCalculationAutomatic
.EnableEvents = True

end with


range("A2:A300").ClearContents
range("A2").FormulaR1C1 = "=IF(ISBLANK(RC[1]),"""",TODAY())"
range("A2").AutoFill _
Destination:=range("A2:A300"), _
Type:=xlFillDefault


"puiuluipui" wrote:

Hi, i have in a macro this part of the code that delete empty rows:

Dim lRow As Long
With Application
.ScreenUpdating = False
.Calculation = xlCalculationManual
.EnableEvents = False
For lRow = Cells.Find(What:="*", After:=[A1], _
SearchDirection:=xlPrevious).Row To 1 Step -1
If WorksheetFunction.CountA(Rows(lRow)) = 0 Then Rows(lRow).Delete
Next lRow
.ScreenUpdating = True
.Calculation = xlCalculationAutomatic
.EnableEvents = True

and then this one:

range("A2:A300").Select
Selection.ClearContents
range("A2").Select

range("A2").Select
ActiveCell.FormulaR1C1 = "=IF(ISBLANK(RC[1]),"""",TODAY())"
Selection.AutoFill Destination:=range("A2:A300"), Type:=xlFillDefault
range("A2:A300").Select
range("A2").Select
The problem is that the second part is blocking the first one. i neet to
delete empty rows and then to add formula.
What am i doing wrong?
Thanks!

Reply With QuoteReply With Quote
  #7   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 468
Default 2 macro help
Hi Rick, i really don't know so much vb. So, how can i implement your code?
Thanks!

"Rick Rothstein" wrote:

The deleting of Rows will cause a problem with the For loop.

Not if you use a For loop that loops from the end of the columns of data
upward to the beginning of the data (using a Step -1 statement to effect the
backward iteration). For example...

For X = LastRow To StartRow Step -1

--
Rick (MVP - Excel)


"Joel" wrote in message
...
when deleting rows use a Do Loop, not a for loop. The deleting of Rows
will
cause a problem with the For loop.

Dim lRow As Long
With Application
.ScreenUpdating = False
.Calculation = xlCalculationManual
.EnableEvents = False

set c = lRow = Cells.Find(What:="*", After:=[A1], _
SearchDirection:=xlPrevious)
if not c is nothing then
LastRow = c

RowCount = LastRow
Do while RowCount = 1


If WorksheetFunction.CountA(Rows(Rowcount)) = 0 Then
Rows(RowCount).Delete
end if
RowCount = RowCount - 1
Loop

.ScreenUpdating = True
.Calculation = xlCalculationAutomatic
.EnableEvents = True

end with


range("A2:A300").ClearContents
range("A2").FormulaR1C1 = "=IF(ISBLANK(RC[1]),"""",TODAY())"
range("A2").AutoFill _
Destination:=range("A2:A300"), _
Type:=xlFillDefault


"puiuluipui" wrote:

Hi, i have in a macro this part of the code that delete empty rows:

Dim lRow As Long
With Application
.ScreenUpdating = False
.Calculation = xlCalculationManual
.EnableEvents = False
For lRow = Cells.Find(What:="*", After:=[A1], _
SearchDirection:=xlPrevious).Row To 1 Step -1
If WorksheetFunction.CountA(Rows(lRow)) = 0 Then Rows(lRow).Delete
Next lRow
.ScreenUpdating = True
.Calculation = xlCalculationAutomatic
.EnableEvents = True

and then this one:

range("A2:A300").Select
Selection.ClearContents
range("A2").Select

range("A2").Select
ActiveCell.FormulaR1C1 = "=IF(ISBLANK(RC[1]),"""",TODAY())"
Selection.AutoFill Destination:=range("A2:A300"), Type:=xlFillDefault
range("A2:A300").Select
range("A2").Select
The problem is that the second part is blocking the first one. i neet to
delete empty rows and then to add formula.
What am i doing wrong?
Thanks!


Reply With QuoteReply With Quote
  #8   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 9,101
Default 2 macro help
I just left out one end if

Sub Macro1()
'
Dim lRow As Long
With Application
.ScreenUpdating = False
.Calculation = xlCalculationManual
.EnableEvents = False

Set c = lRow = Cells.Find(What:="*", After:=[A1], _
SearchDirection:=xlPrevious)
If Not c Is Nothing Then
LastRow = c

RowCount = LastRow
Do While RowCount = 1


If WorksheetFunction.CountA(Rows(RowCount)) = 0 Then
Rows(RowCount).Delete
End If
RowCount = RowCount - 1
Loop

.ScreenUpdating = True
.Calculation = xlCalculationAutomatic
.EnableEvents = True
End If
End With


Range("A2:A300").ClearContents
Range("A2").FormulaR1C1 = "=IF(ISBLANK(RC[1]),"""",TODAY())"
Range("A2").AutoFill _
Destination:=Range("A2:A300"), _
Type:=xlFillDefault


End Sub


"puiuluipui" wrote:

Hi Joel, i receive this error after i paste your code:
Compile error:
End With without with.

What is wrong?
Thanks!

"Joel" wrote:

when deleting rows use a Do Loop, not a for loop. The deleting of Rows will
cause a problem with the For loop.

Dim lRow As Long
With Application
.ScreenUpdating = False
.Calculation = xlCalculationManual
.EnableEvents = False

set c = lRow = Cells.Find(What:="*", After:=[A1], _
SearchDirection:=xlPrevious)
if not c is nothing then
LastRow = c

RowCount = LastRow
Do while RowCount = 1


If WorksheetFunction.CountA(Rows(Rowcount)) = 0 Then
Rows(RowCount).Delete
end if
RowCount = RowCount - 1
Loop

.ScreenUpdating = True
.Calculation = xlCalculationAutomatic
.EnableEvents = True

end with


range("A2:A300").ClearContents
range("A2").FormulaR1C1 = "=IF(ISBLANK(RC[1]),"""",TODAY())"
range("A2").AutoFill _
Destination:=range("A2:A300"), _
Type:=xlFillDefault


"puiuluipui" wrote:

Hi, i have in a macro this part of the code that delete empty rows:

Dim lRow As Long
With Application
.ScreenUpdating = False
.Calculation = xlCalculationManual
.EnableEvents = False
For lRow = Cells.Find(What:="*", After:=[A1], _
SearchDirection:=xlPrevious).Row To 1 Step -1
If WorksheetFunction.CountA(Rows(lRow)) = 0 Then Rows(lRow).Delete
Next lRow
.ScreenUpdating = True
.Calculation = xlCalculationAutomatic
.EnableEvents = True

and then this one:

range("A2:A300").Select
Selection.ClearContents
range("A2").Select

range("A2").Select
ActiveCell.FormulaR1C1 = "=IF(ISBLANK(RC[1]),"""",TODAY())"
Selection.AutoFill Destination:=range("A2:A300"), Type:=xlFillDefault
range("A2:A300").Select
range("A2").Select
The problem is that the second part is blocking the first one. i neet to
delete empty rows and then to add formula.
What am i doing wrong?
Thanks!
Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
Macro Help Needed - Excel 2007 - Print Macro with Auto Sort Gavin Excel Worksheet Functions 0 May 17th 07 01:20 PM
My excel macro recorder no longer shows up when recording macro jack Excel Discussion (Misc queries) 3 February 5th 07 08:22 PM
using a cell value to control a counter inside a macro and displaying macro value ocset Excel Worksheet Functions 1 September 10th 06 05:32 AM
Macro needed to Paste Values and prevent Macro operation thunderfoot Excel Discussion (Misc queries) 1 June 11th 05 12:44 AM


All times are GMT +1. The time now is 03:41 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement