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?
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.
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.
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:
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.
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 rngData
como 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 .EntireColumn
antes, dataColumn
é definido como um intervalo de células em rngData
usando Range(rngData.Cells(1, i), rngData.Cells(rngData.Rows.Count, i))
. Ou seja, células na _i_ésima coluna para linhas rngData
de 1 ao número de linhas de rngData
.
Para um teste, aqui está uma seleção inicial:
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:
Este artigo é coletado da Internet.
Se houver alguma infração, entre em [email protected] Delete.
deixe-me dizer algumas palavras