Excel to be the last of me! VB + Excel help needed

[S@njay]

Thanks for reading this post!

I'm trying to delete a series of rows using a loop to go through a range of cells.
My code works on one sheet, however, when i want to "goto" another sheet it comes up with an error

 Private Sub CommandButton1_Click()
Dim i As Integer
For i = 2 To 6
Sheets("Sheet1").Activate
If ActiveSheet.Cells(i, 1) = "" Then
Sheets("Sheet2").Activate
ActiveSheet.Range(Cells(i, 4), Cells(i, 5)).Select ' ERROR IS ON THIS LINE?
Selection.Clear
End If
Next i
End Sub

Why is this? Or am i missing something?!

Thanks for your help

 

[noob]

Sub test()

Dim i As Integer
    
    For i = 2 To 6
        Sheets("Sheet1").Activate
        If ActiveSheet.Cells(i, 1) = "" Then
            Sheets("Sheet2").Activate
            ActiveSheet.Range(Cells(i, 4), Cells(i, 5)).Select ' ERROR IS ON THIS LINE?
            Selection.Clear
        End If
    Next i
End Sub

Works for me? I created a macro called test and popped the code in there. Using debug (step into) everything looks ok.

 

[S@njay]

I still got the error

I am using 2003 excel, mabey thats causing a problem?

I am sure i'm using Excel XP at work, and it dont work there!

I am using this code now, but its a bit slow..

 Dim i As Integer
Dim j As Integer
Dim x As Integer
 x = 32
For i = 3 To x
	Sheets("Employee").Activate
		If ActiveSheet.Cells(i, 1) = "" Then
		
				Sheets("Jan").Activate
				For j = 4 To x
				ActiveSheet.Cells(i + 1, j).Select
				Selection.ClearContents
				Next j
				
				Sheets("Feb").Activate
				For j = 4 To x
				ActiveSheet.Cells(i + 1, j).Select
				Selection.ClearContents
				Next j

	   'and the same till DEC
				
			 
			   
		End If
Next i
End Sub

 

[noob]

I'm using Excel 2003, going to have a bath now will have a look later. By the way can you post a screenshot of the error.

 

[noob]

Sub Test()

Dim i As Integer
Dim j As Integer
Dim x As Integer
 
x = 32

For i = 3 To x
    Sheets("Employee").Activate
        If ActiveSheet.Cells(i, 1) = "" Then
        
                Sheets("Jan").Activate
                For j = 4 To x
                    ActiveSheet.Cells(i + 1, j).Select
                    Selection.ClearContents
                Next j
                
                Sheets("Feb").Activate
                
                For j = 4 To x
                    ActiveSheet.Cells(i + 1, j).Select
                    Selection.ClearContents
                Next j

       'and the same till DEC
        End If
Next i
End Sub

Created three sheets, Employee, Jan, Feb and then pasted the above code into a macro called 'Test' and again it works. You need to post a screenshot of the error.

 

[ByteJuggler]

I'm not saying this is the issue, but I'd nevertheless suggest avoiding any reliance on "ActiveSheet" and friends. Just reference the sheets you want to work with directly. (Activehseet and so on can be affected during macro operations by other events, e.g. user clicks and sometimes by other state changes in the Excel object, e.g whether screen updates is enabled and so on. I tend to therefore avoid reliance on "interactive" state properties such as ActiveSheet etc.)

 

[ByteJuggler]

Try this:

Option Explicit

Sub Macro1()
'
' Macro1 Macro
' Macro recorded 19/12/2006 by Walter Prins
'
  Application.DisplayAlerts = False
  Application.ScreenUpdating = False
  
  Dim sheet As Worksheet
        
  For Each sheet In ActiveWorkbook.Worksheets
    DeleteEmptyRowsIn sheet.Rows("1:10")
  Next
  
  Application.ScreenUpdating = True
  Application.DisplayAlerts = True
End Sub


Private Sub DeleteEmptyRowsIn(ARange As Range)
  ' Step through the supplied range, inspect the first cell in each row of the range
  ' and if it's Empty, delete that row.
  Dim row As Range
  For Each row In ARange.Rows
    If row.Cells(1, 1).Value = Empty Then
      row.Delete
    End If
  Next
End Sub

Add the bottom sub to a suitable module, then add a macro with a hot-key, and call the sub as shown above. Note, this actually deletes the rows, as opposed to clearing them. Change this to suit, if required.

Edit: Updated to process all worksheets in the current workbook, for a fixed range of rows, and added code to display display while working.