Looking For An Excel Specialist

[Jahil]

hello.

I am trying to find a way to hide automatically lines in a worksheet based on the fact that one cell in the line is equal to 0 or blank.....

Any idea will be appreciated.

[dave_boo]

Use this link, only change the "$B$1" to 0 and the "A2:A10" to the appropiate value.

[Jahil]

Use this link, only change the "$B$1" to 0 and the "A2:A10" to the appropiate value.

thanks for your quick answer Dave....

I will try it and i keep you updated

[rdefazio]

hello.

I am trying to find a way to hide automatically lines in a worksheet based on the fact that one cell in the line is equal to 0 or blank.....

Any idea will be appreciated.

Try using this code in the form of a macro. Run the macro to make rows disappear if cells are blank or if a cell contains a specific value.

<FONT face="Courier New"></FONT>Sub Macro1()
Dim rowsToScanLong As Long
Dim startingCellToScanLong As Long
Dim endingCellToScanLong As Long
Dim columnIndexNumberLong As Long
Dim testValueLong As Long
Dim currentRowLong As Long
Dim currentCellLong As Long

' In the next line you can define the index number of a specific column that will contain the test value.
' If you wish to test all columns in the range of populated cells, simply set the next lines value to 0.
columnIndexNumberLong = 0

' In the next line you must define the value you wish to use as the value that will trigger the hiding of the
' row.
testValueLong = 0

' The rest of the code below simply uses the values you have set in the above lines.

' In the next three lines the following is done:
' 1. The top left cell is given focus.
' 2. The bottom row of the populated rows is found.
' 3. The row number of the last row is assigned to the rowsToScanLong variable.
Range("A1").Select
Selection.End(xlDown).Select
rowsToScanLong = Selection.Row

If columnIndexNumberLong = 0 Then

	' if a specific column has not been defined, then detect the number of columns containing data in the first row.
	Range("A1").Select
	Selection.End(xlToRight).Select
	startingCellToScanLong = 1
	endingCellToScanLong = Selection.Column

Else

	' otherwise set the beginning and ending columns to the defined column.
	startingCellToScanLong = columnIndexNumberLong
	endingCellToScanLong = columnIndexNumberLong

End If

' Process each row in the spreadsheet that has data in the test cell.
For currentRowLong = 1 To rowsToScanLong

	' Process each cell in the row within the range that has been defined or detected.
	For currentCellLong = startingCellToScanLong To endingCellToScanLong

		' Check to see if the test cell contains the test value OR if the test cell is empty
		If Cells(currentRowLong, currentCellLong) = testValueLong Or _
		   IsEmpty(Cells(currentRowLong, currentCellLong)) Then

			' If so, then select the row.
			Rows(currentRowLong & ":" & currentRowLong).Select

			' And hide it.
			Selection.EntireRow.Hidden = True

			' exit the cell scan
			Exit For
		End If

	' go to next cell in cell scanning range
	Next

' go to the next row in row scanning range
Next</FONT></P> <P><FONT face="Courier New">End Sub

[Digitalbanana]

hello.

I am trying to find a way to hide automatically lines in a worksheet based on the fact that one cell in the line is equal to 0 or blank.....

Any idea will be appreciated.

Why not just use the filter option?