Strip away empty columns on the left side of a named range

code_life

Let's suppose that rangeData refers to a range of cells in a spreadsheet.

If the right most column is empty, I can strip it away by doing this:

rangeData = rangeData.CurrentRegion

But what if the left most column of rangeData is empty?

How do I strip away that column and have rangeData refer to only the columns with values?

Peter Constable

Weird: by the way CurrentRegion is described in the documentation, I would have expected it not to include empty rows on top or empty columns on the left.

You could go through the columns from the left looking for the first non-empty column, and then set the range based on that as follows:

    Dim i As Long
    Dim firstNonEmptyColumn As Long
    For i = 1 To rngData.Columns.Count
        If WorksheetFunction.CountA(rngData.Cells(1, i).EntireColumn) <> 0 Then
            firstNonEmptyColumn = i
            Exit For
        End If
    Next i
    Set rngData = Range(rngData.Cells(1, firstNonEmptyColumn), rngData.Cells(rngData.Rows.Count, rngData.Columns.Count))

You could do something similar for empty rows on top.


Update

Let me clarify my assumptions and provide more details: I'm assuming that the starting condition is rngData set to contiguous range that contains non-empty cells surrounded by some rows/columns that are empty. And that the goal is to change rngData so that there are no empty columns on the left.

Also assumed: there are no other non-empty cells above or below on the sheet outside the range of rngData. (See below for a different assumption.)

To initialize rngData, I'll start with a selection that contains the non-empty cells.

insira a descrição da imagem aqui

Then I'll run the following—at the end I make rngData the selection to easily visualize the change to rngData:

Sub test()
    Dim rngData As Range
    Dim i As Long
    Dim firstNonEmptyColumn As Long
    
    Set rngData = Selection
    
    For i = 1 To rngData.Columns.Count
        If WorksheetFunction.CountA(rngData.Cells(1, i).EntireColumn) <> 0 Then
            firstNonEmptyColumn = i
            Exit For
        End If
    Next i
    Set rngData = Range(rngData.Cells(1, firstNonEmptyColumn), rngData.Cells(rngData.Rows.Count, rngData.Columns.Count))
    rngData.Select
End Sub

Here's the resulting selection:

insira a descrição da imagem aqui

Note that .CurrentRegion will use the first (top left) cell as its starting reference point. If that cell is empty (as in my example here), then the current region for that will be only that cell. .CurrentRegion is really meant to be used in cases in which the first cell is non-empty.

But what I showed used WorksheetFunction.CountA() on all the cells in a column to determine if the column is (non)empty (It could also be used with .EntireRow.) So it isn't limited by the first cell in the range.


Second update

In the previous update, it was assumed that there aren't non-empty cells above or below on the sheet outside the range of rngData. Because of that assumption, .EntireColumn was used to check for non-empty cells.

Se essa não for uma suposição apropriada para a situação, o teste para colunas com células não vazias pode ser limitado apenas às células dentro do intervalo de rngData. A seguinte sub será inicializada rngDatacomo antes, mas verifique se há colunas não vazias limitadas apenas às células dentro de rngData:

Sub test()
    Dim rngData As Range
    Dim dataColumn As Range
    Dim i As Long
    Dim firstNonEmptyColumn As Long
    
    Set rngData = Selection
    
    For i = 1 To rngData.Columns.Count
        Set dataColumn = Range(rngData.Cells(1, i), rngData.Cells(rngData.Rows.Count, i))
        If WorksheetFunction.CountA(dataColumn) <> 0 Then
            firstNonEmptyColumn = i
            Exit For
        End If
    Next i
    Set rngData = Range(rngData.Cells(1, firstNonEmptyColumn), rngData.Cells(rngData.Rows.Count, rngData.Columns.Count))
    rngData.Select
End Sub

Em vez de usar .EntireColumnantes, dataColumné definido como um intervalo de células em rngDatausando Range(rngData.Cells(1, i), rngData.Cells(rngData.Rows.Count, i)). Ou seja, células na _i_ésima coluna para linhas rngDatade 1 ao número de linhas de rngData.

Para um teste, aqui está uma seleção inicial:

insira a descrição da imagem aqui

Observe as células não vazias abaixo da seleção para a qual é usada rngData. Depois de executar esse sub, aqui está a seleção resultante:

insira a descrição da imagem aqui

Este artigo é coletado da Internet.

Se houver alguma infração, entre em [email protected] Delete.

editar em
0

deixe-me dizer algumas palavras

0comentários
loginDepois de participar da revisão

Artigos relacionados

Google sheet, select columns within a named range

android navigation view empty space before(left side of) menu item

Insert string("+") into named-range merged-cell but only if empty

Will an empty class be optimized away

Removing all empty columns and rows in data.frame when rows don't go away

Drop columns containing specific characters in title whilst moving values under those columns to nearest left side column

How to pad char array with empty spaces on left and right hand side of the text

Check if Named Range is equal to another Named Range

MS SQL 2012 : In SQL Shift columns to left side if column contains 0

Which formula to use if I were to compare values from multiple columns with corresponding values within a named range?

Named Range String limitations

Strip certain content of columns in multiple columns

Is there a way to add forced empty values to columns on the sink side in a copy data flow?

flutter listview and columns side by side

Will C# compiler strip out empty methods?

Set image on left side of dialog

table header on left side in Bootstrap?

Bootstrap - label on the left side of checkbox

How to modify the left side of a formula?

Reference Specific Row in Named Range within another Named Range

Excel - Unicode named cell range

Adding Named Range reference in VBA

How to filter and copy a named range?

Dynamic Named Range - Disparate Cells

Parse existing CSV file and strip certain columns

Statistics of multiple similarly named columns

range on multiple columns in cassandra

Using query, Import Range and named range to either label the named range or ignore the header

Empty columns in responsive grid?

TOP lista

quentelabel

Arquivo