logo
Tags down

shadow

How Hide columns according to a cell value


By : David Casellas
Date : October 18 2020, 03:08 PM
I wish this help you One change event with multiple conditions tests within an IF statement, using ElseIf. Without writing it all for you, the following it the structure and key elements. There are plenty of examples on stack overflow to help.
code :
Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("B2")) Is Nothing Then

        'Code to unhide all columns goes here.

        'Then test the contents of B2
        If Target = 1 Then
            Columns("I:BV").EntireColumn.Hidden = True
        ElseIf Target = 2 Then 
            Columns("O:BV").EntireColumn.Hidden = True
        ElseIf Target = 3 Then ......'Continue with rest of conditions
        End If
   End If

End Sub


Share : facebook icon twitter icon

Hide columns based on cell value


By : jpX
Date : March 29 2020, 07:55 AM
To fix the issue you can do In my google spreadsheet A, I use a combination of the TRANSPOSE and IMPORTRANGE formulas to import data from calendar spreadsheet B, in order to fill out a working schedule for events. Because on each date there are 3 event slots, which are not always filled, I am getting a lot of obsolete columns. , The following code does the trick:
code :
function onOpen() {
  // get active spreadsheet
  var ss = SpreadsheetApp.getActiveSpreadsheet();

  // create menu
  var menu = [{name: "Hide columns", functionName: "hideColumn"},
    {name: "Show all columns", functionName: "showColumn"}];

  // add to menu
  ss.addMenu("Check", menu);
}

function hideColumn() {
  // get active spreadsheet
  var ss = SpreadsheetApp.getActiveSpreadsheet();

  // get first sheet
  var sheet = ss.getSheets()[0];

  // get data
  var data = sheet.getDataRange();

  // get number of columns
  var lastCol = data.getLastColumn()+1;

  Logger.log(lastCol);

  // itterate through columns
  for(var i=1; i<lastCol; i++) {
     if(data.getCell(3, i).getValue() == '') {
        sheet.hideColumns(i);
     }
  }
}

function showColumn() {
  // get active spreadsheet
  var ss = SpreadsheetApp.getActiveSpreadsheet();

  // get first sheet
  var sheet = ss.getSheets()[0];

  // get data
  var data = sheet.getDataRange();

  // get number of columns
  var lastCol = data.getLastColumn();

  // show all columns
  sheet.showColumns(1, lastCol);
}

Hide columns when cell is empty in google script


By : user2820935
Date : March 29 2020, 07:55 AM
around this issue I think your for loop should be like this if you want to go through every column.
code :
for (var i=0; i < values[0].length; i++){
function hideEmptyColumns() {
var ss = SpreadsheetApp.getActive(),
    sheet = ss.getSheets()[0],
    fullRange = sheet.getRange(2, 1, sheet.getLastRow(), sheet.getMaxColumns());
 var data = fullRange.getValues(),
    numCol = fullRange.getNumColumns();
 for (var i = 0; i < numCol; i++) {
    var col = data.map(function (v) {
        return v[i];
    })
        .filter(function (d) {
            return d !== '';
        });
    if (col.length == 0) sheet.hideColumns(i + 1);
    }
}
function hideEmptyColumns2() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheets()[0];
var range = sheet.getRange(2, 1, 1, sheet.getMaxColumns());
//get the values to those columns
var values = range.getValues();
//go through every column
for (var i = 0; i < values[0].length; i++) {
    Logger.log(values[0][i])
    //if column value is equal to empty  
    if (values[0][i] == "") {
        //hide that column
        sheet.hideColumns(i + 1);
    }
}
}

Hide columns if cell has a given value


By : Zanidd Kyhu
Date : March 29 2020, 07:55 AM
With these it helps I would like to hide some columns if A1 changes but I am afraid that the function Worksheet_Change does not work. , change to
code :
If Target.Address = "$A$1" Then 
If Not Intersect(Target, Range("A1")) Is Nothing Then 

Is there a way to search and hide all columns that do not contain a value entered into a given cell?


By : Ramanathan Kannappan
Date : March 29 2020, 07:55 AM
wish helps you I need to Hide all rows that do not contain partial value typed into a particular cell but cannot figure out how to define the cell as the search value and not a particular string using VBA in Excel. , Using Like should do the trick:
code :
For a = 2 To 200
    If Worksheets("Purchase Log").Cells(a, 2).Value Like "*" & Cells(1, 35) & "*" Then
        Worksheets("Purchase Log").Rows(a).Hidden = False
    Else
        Worksheets("Purchase Log").Rows(a).Hidden = True
    End If
Next

Is it possible to hide certain cells (not rows or columns) based on a cell value?


By : Murtaza Quisar
Date : October 05 2020, 01:00 AM
will be helpful for those in need No it is not possible to hide cells without hiding the entire column or row in which it is found.
You can use Conditional Formatting to make the cells appear blank by using the custom format of ;;; which will show nothing in the cell but the formula bar will show the value.
Related Posts Related Posts :
  • Excel - splitting multiline cells into columns but keep relevant data grouped on same column
  • More efficient method in order to replicate function VBA/Excel
  • How to select multiple cells in VBA script
  • Excel VBA "Like" not Selecting for "*M*"
  • Find and replace the whole string when a partial match is achieved multiple sheets
  • Removing Rows - Code Too Slow
  • Lookup a date, based on a variable, and then add a number of days to that date, to give a new date
  • Error while creating Pivot Table automatically
  • Find sheet name of a worksheet using relative reference
  • Excel Formula to work out incremental figure defined by various monthly results
  • Best way to check for duplicates in VBA
  • Error 1004: Unable to Get CountIf Property
  • Selecting the correct starting point for an offset sum
  • Deleting rows when two blank rows are concurrent
  • VBA: Renaming the active sheet
  • VBA: Creating a Pivot Table
  • Copy worksheet to new workbook and change the name of the worksheet to a compose name
  • Dynamic Referencing in VBA Formula
  • Sort Dictionary Keys using ArrayList
  • Excel cell color formatting
  • Excel: Copying rows to table in another sheet using VBA
  • use macro enabled Excel file in OneDrive
  • Faster VBA to hide rows
  • Two Pivot Tables in the same excel sheet
  • For loop setting Font and Interior of Range taking way too long
  • shadow
    Privacy Policy - Terms - Contact Us © soohba.com