Excel 2010 Macros

[Robert Drummond]

I am a beginner with Macros for Excel. I want to create a Macro to hide rows in a spread-sheet if there is a blank in column 7. I found the following Macro on a website that did this which I think I have correctly entered.


Sub HURows()
BeginRow = 3
EndRow = 416
ChkCol = 7

For RowCnt = BeginRow To EndRow
If Cells(RowCnt, ChkCol).Value = “” Then
Cells(RowCnt, ChkCol).EntireRow.Hidden = True
Else
Cells(RowCnt, ChkCol).WntireRow.Hidden = False
End If
Next RowCnt
End Sub

It wont work. I have the workbook in Macro enabled form but when I click Developer, Macro, Run I get the message "Object doesn't support this property or method". I have tried changing the "" blank to a number as the column has currency amounts in it.

 

[cdchapman]

Hi Robert,

You are nearly there with your macro. The error is due to the fact that you haven't declared your variables (BeginRow, EndRow and ChkCol) quite correctly - you need to precede each variable with either the DIM or CONST keyword (DIM indicating that the following word is a variable, and CONST indicating a constant value - ie one which never changes). In your example, either could be used, although I feel CONST would be better.

So the re-written macro code would be (changes highlighted in red):

[COLOR=#333333]Sub HURows()[/COLOR]
[COLOR=#ff0000]CONST BeginRow = 3
CONST EndRow = 416
CONST ChkCol = 7[/COLOR]

[COLOR=#333333]For RowCnt = BeginRow To EndRow[/COLOR]
[COLOR=#333333]  If Cells(RowCnt, ChkCol).Value = “” Then[/COLOR]
[COLOR=#333333]    Cells(RowCnt, ChkCol).EntireRow.Hidden = True[/COLOR]
[COLOR=#333333]  Else[/COLOR]
[COLOR=#333333]    Cells(RowCnt, ChkCol).EntireRow.Hidden = False[/COLOR]
[COLOR=#333333]  End If[/COLOR]
[COLOR=#333333]Next RowCnt[/COLOR]
[COLOR=#333333]
End Sub
[/COLOR]

By the way, I don't know if this will interest you, but there is a way to get Excel to loop through each row until it finds the first blank row with no data. This would eliminate the need for the BeginRow and EndRow constants and would also cater for situations in the future if rows of data were added or deleted. This does, however need a column to work with that has no gaps in the data (ie each cell in the column from the first row to the end row has a value).

If this is of help to you, let me know the column in your data that can be used, and I'll re-write the code for you.

Hope this helps

Chris

 

[Peter_SSs]

Welcome to the MrExcel board!

Although it is a good idea to declare your variables, it is not compulsory and it is not why you were getting that error.
You were getting the error because of a spelling mistake in your code.
That should be "Entire" not "Wntire"

Sub HURows()
BeginRow = 3
EndRow = 416
ChkCol = 7

For RowCnt = BeginRow To EndRow
If Cells(RowCnt, ChkCol).Value = “” Then
Cells(RowCnt, ChkCol).EntireRow.Hidden = True
Else
Cells(RowCnt, ChkCol).WntireRow.Hidden = False
End If
Next RowCnt
End Sub

It wont work. I have the workbook in Macro enabled form but when I click Developer, Macro, Run I get the message "Object doesn't support this property or method". I have tried changing the "" blank to a number as the column has currency amounts in it.

 

[Rick Rothstein]

By the way, I don't know if this will interest you, but there is a way to get Excel to loop through each row until it finds the first blank row with no data. This would eliminate the need for the BeginRow and EndRow constants and would also cater for situations in the future if rows of data were added or deleted. This does, however need a column to work with that has no gaps in the data (ie each cell in the column from the first row to the end row has a value).

You can get the last row with a displayed value without knowing what column contains it...

LastRowWithDisplayValue = Cells.Find("*", , xlValues, , xlRows, xlPrevious).Row

Also, assuming Column G contains constants (that is, not formulas), the OP can use this non-looping macros to hide the rows where Column G is blank...

Sub HideRowsIfBlankInColumnG()
  Dim LastRow As Long
  LastRow = Cells.Find("*", , xlValues, , xlRows, xlPrevious).Row
  Rows("3:" & LastRow).Hidden = False
  On Error GoTo NoBlanks
  Range("G3:G" & LastRow).SpecialCells(xlBlanks).EntireRow.Hidden = True
NoBlanks:
End Sub