Excel 2010 Macros

Robert Drummond

New Member
Joined
Feb 7, 2016
Messages
1
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.
 

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.
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):

Code:
[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
 
Upvote 0
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.
 
Upvote 0
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...

Code:
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...

Code:
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
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,251
Members
448,556
Latest member
peterhess2002

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top