CODEWITHSUNDEEP
vbaexcel
SANDY
SANDY

Reputation: 5

Change the Range Into First and Last column with Values

A little help for my working file is appreciated..

Below is my code, What I want is to set this formula to fixed Range.. Sheets("Unire").Range("CB4:HJ4")) What I mean is The Range will start from (Row 4:Col CB) to Last Column in Row 4 with Values.

Sub Unire()
    Dim cell As Range
    Application.ScreenUpdating = False
    Sheets("Unire").Activate
    For Each cell In Intersect(ActiveSheet.UsedRange, Sheets("Unire").Range("CB4:HJ4"))
        cell.EntireColumn.Hidden = cell.value <> Sheets("Command").Range("B5") And Not IsEmpty(cell)
    Next cell
    Application.ScreenUpdating = True

End Sub

Upvotes: 0

Views: 303

Answers (2)

user3598756
user3598756

Reputation: 29421

should your row 4 not empty cells be filled with "constant" (i.e. not deriving from formulas) values, then you could enhance your code speed by iterating only through not blank cells by means of SpecialCells() method of Range object

furthermore you'd better store Sheets("Command").Range("B5").Value in a helper variable once and for all instead of accessing it at every iteration

Option Explicit

Sub Unire()
    Dim cell As Range
    Dim refVal As Variant

    refVal = Sheets("Command").Range("B5").Value '<--| store reference value for subsequent comparisons
    On Error GoTo ExitSub '<--| make sure to properly exit this sub should sunsequent statements (namely 'SpecialCells' one) raise any error
    Application.ScreenUpdating = False
    With Sheets("Unire")
        For Each cell In .Range("CB4", .cells(4, .columns.Count).End(xlToLeft)).SpecialCells(xlCellTypeConstants)  ' <--| reference not empty cells from CB4 to last not-empty cell in row 4
            cell.EntireColumn.Hidden = cell.Value <> refVal
        Next
    End With

ExitSub:
    Application.ScreenUpdating = True
End Sub

Upvotes: 0

A.S.H
A.S.H

Reputation: 29332

This should do:

Sub Unire()
    Dim cell As Range
    Application.ScreenUpdating = False
    With Sheets("Unire")
        For Each cell In .Range("CB4", .Range("XFD4").End(xlToLeft)) ' <-- from CB4 to last non-empty on row 4
            cell.EntireColumn.Hidden = cell.value <> Sheets("Command").Range("B5").value And Not IsEmpty(cell)
        Next cell
    End With
    Application.ScreenUpdating = True
End Sub

Upvotes: 1

Related Questions