EndRow based on a cell value not location

jomayo

New Member
Joined
Mar 29, 2011
Messages
3
Hi. I’ve spent most of this day looking through Excel help and the internet trying to find a solution. I am VERY new at VBA, so please bear with me. I’m using VBA to hide rows in a sheet, and the column that determines whether or not to hide a row has references to another sheet throughout. I’d like for the code to stop at a certain point (either where a column value returns with zero OR where it would equal another cell in that row) instead of having to run the macro through cells it doesn’t need to look at. Below is what I have so far. I think I need to change the “EndRow =” reference, but I’m not sure to what. Thanks in advance for the help.<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:eek:ffice:eek:ffice" /><o:p></o:p>
<o:p> </o:p>
Sub HURows()<o:p></o:p>
BeginRow = 2<o:p></o:p>
EndRow = 1000<o:p></o:p>
ChkCol = 4<o:p></o:p>
<o:p> </o:p>
For RowCnt = BeginRow To EndRow<o:p></o:p>
If Cells(RowCnt, ChkCol).Value > 0 Then<o:p></o:p>
Cells(RowCnt, ChkCol).EntireRow.Hidden = True<o:p></o:p>
Else<o:p></o:p>
Cells(RowCnt, ChkCol).EntireRow.Hidden = False<o:p></o:p>
End If<o:p></o:p>
Next RowCnt<o:p></o:p>
End Sub<o:p></o:p>
 

Some videos you may like

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.

Dryver14

Well-known Member
Joined
Mar 22, 2010
Messages
2,396
Code:
Sub HURows()
FinalRow = Cells(Rows.Count, 4).End(xlUp).Row
For i = 2 To FinalRow
If Cells(i, 4).Value > 0 Then
    Cells(i, 4).EntireRow.Hidden = True
End If
Next i
End Sub

this only takesthe macro to the last row, if you wanted a value to stop running then you could put something like

If Cells (i , 4).value = 33 then exit sub
 

Watch MrExcel Video

Forum statistics

Threads
1,108,969
Messages
5,525,963
Members
409,673
Latest member
Riseee

This Week's Hot Topics

Top