How do you show only one row on Spreadsheet using VBA

D

Dennis

For a spreadsheet consisting of many rows I have a VBA routine that finds the
row of interest. Using VBA, how do you display only that row and the
spreadsheet header making all other rows invisible?

I didn't see an option in the AutoFilter to select a just a row number.
 
F

Frank Kabel

Hi
you could set the .hide property to TRUE for all other rows

--
Regards
Frank Kabel
Frankfurt, Germany

For a spreadsheet consisting of many rows I have a VBA routine that finds the
row of interest. Using VBA, how do you display only that row and the
spreadsheet header making all other rows invisible?

I didn't see an option in the AutoFilter to select a just a row
Click to expand...
number.
 
F

Frank Kabel

Hi
try something like

sub foo()
dim rowndx
rowndx=5 'calculate this

rows(2:100).hidden=true
rows(rowndx).hidden=false
end sub
 
D

Dennis

Hi Frank

rows(2:100).hidden=true gave me an error on the ":"

I tried this but it also didn't work.

Dim curwk As Worksheet

Sheets(1).Activate
Set curwk = Sheets(1)

With curwk
.Cells(Rows.Count, "a").End(xlUp).Row.Hidden = True
.Rows(kk).Hidden = False
End With

Dennis
 
D

Dave Peterson

Try:

rows("2:100").hidden = true



Hi Frank

rows(2:100).hidden=true gave me an error on the ":"

I tried this but it also didn't work.

Dim curwk As Worksheet

Sheets(1).Activate
Set curwk = Sheets(1)

With curwk
.Cells(Rows.Count, "a").End(xlUp).Row.Hidden = True
.Rows(kk).Hidden = False
End With

Dennis
Click to expand...
 
D

Dennis

Hi Dave,

I tried this because the last row is variable

With curwk
.Rows.EntireRow.Hidden = True
.Rows(1).EntireRow.Hidden = False
.Rows(kk).EntireRow.Hidden = False
End With
Range("A1").Select

This hide all the rows and showed only rows 1 and kk but despite the
Range("A1").Select, the window was scrolled down so I couldn't see the two
visible lines. I had to scroll to the top by hand.

Is there another way to scroll to the top automatically?

Thanks.

Dennis
 
D

Dennis

I finally found the correct solution that is not scrolled below the 2 visible
lines. here it is:

Dim curwk As Worksheet
Dim rng as Range
Dim LastRow as long, LastCol as Long
Set curwk = Sheets(1)
LastRow = .Cells(Rows.Count, "a").End(xlUp).Row
LastCol = .Range("A2").End(xlToRight).Column
With curwk
Set rng = .Range("A1").Offset(2, 0).Resize(LastRow - 1, LastCol)
rng.EntireRow.Hidden = True
.Rows(kk).Hidden = False
.Range("A1").Select
End With

Notice I had to use "EntireRow" in the range or this would not have worked!

To make all rows visible again I created an Icon to run the following code:

Sub MakeVisible()
On Error Resume Next

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Rows("1:1").Select
Selection.AutoFilter
Selection.AutoFilter field:=1, Criteria1:="All"
Sheets(1).AutoFilterMode = False
Range("a1").Select
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub

Dennis
 
B

BrianB

How about this ........

Code
-------------------

Sub SelectedRowOnly()
Dim MyRow As Long
Dim LastRow As Long
Dim Rg As String
'-----------------------
MyRow = ActiveCell.Row
LastRow = Range("A65536").End(xlUp).Row
Rg = "2:" & LastRow
ActiveSheet.Rows(Rg).EntireRow.Hidden = True
ActiveSheet.Rows(MyRow).EntireRow.Hidden = False
Application.Goto reference:=ActiveSheet.Range("A1"), Scroll:=True
End Sub

-------------------
 
D

Dennis

That worked also. Thanks BrianB!

I changed the sub to process the row that another vba routine finds as:

Sub SelectedRowOnly(kk as long)
Dim LastRow As Long
Dim Rg As String
'-----------------------
LastRow = Range("A65536").End(xlUp).Row
Rg = "2:" & LastRow
ActiveSheet.Rows(Rg).EntireRow.Hidden = True
ActiveSheet.Rows(kk).EntireRow.Hidden = False
Application.Goto reference:=ActiveSheet.Range("A1"), Scroll:=True
End Sub

Your routine can also be used to unhide the spreadsheet when the user wants to
see the whole spreadsheet again as in the sub MakeVisible below.

Sub MakeVisible()
On Error Resume Next

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

Dim LastRow As Long
Dim Rg As String
'-----------------------
LastRow = Range("A65536").End(xlUp).Row
Rg = "1:" & LastRow
ActiveSheet.Rows(Rg).EntireRow.Hidden = False
Application.Goto reference:=ActiveSheet.Range("A1"), Scroll:=True

Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub

Thanks again for the neat compact code!

Dennis
 
Top