Выбрать непустые ячейки excel vba

I’m just beginning to dive into VBA and I’ve hit a bit of a roadblock.

I have a sheet with 50+ columns, 900+ rows of data. I need to reformat about 10 of those columns and stick them in a new workbook.

How do I programmatically select every non-blank cell in a column of book1, run it through some functions, and drop the results in book2?

asked May 4, 2009 at 18:47

I know I’m am very late on this, but here some usefull samples:

'select the used cells in column 3 of worksheet wks
wks.columns(3).SpecialCells(xlCellTypeConstants).Select

or

'change all formulas in col 3 to values
with sheet1.columns(3).SpecialCells(xlCellTypeFormulas)
    .value = .value
end with

To find the last used row in column, never rely on LastCell, which is unreliable (it is not reset after deleting data). Instead, I use someting like

 lngLast = cells(rows.count,3).end(xlUp).row

answered Sep 26, 2009 at 11:14

If you are looking for the last row of a column, use:

Sub SelectFirstColumn()
   SelectEntireColumn (1)
End Sub

Sub SelectSecondColumn()
    SelectEntireColumn (2)
End Sub

Sub SelectEntireColumn(columnNumber)
    Dim LastRow
    Sheets("sheet1").Select
    LastRow = ActiveSheet.Columns(columnNumber).SpecialCells(xlLastCell).Row

    ActiveSheet.Range(Cells(1, columnNumber), Cells(LastRow, columnNumber)).Select
End Sub

Other commands you will need to get familiar with are copy and paste commands:

Sub CopyOneToTwo()
    SelectEntireColumn (1)
    Selection.Copy

    Sheets("sheet1").Select
    ActiveSheet.Range("B1").PasteSpecial Paste:=xlPasteValues
End Sub

Finally, you can reference worksheets in other workbooks by using the following syntax:

Dim book2
Set book2 = Workbooks.Open("C:book2.xls")
book2.Worksheets("sheet1")

answered May 4, 2009 at 19:18

For me the best way to proceed was to:

1. Create a new Excel Table
2. AutoFilter it by the parameter Criterial:="<>"

An example of the code would be:

Sub ExampleFilterCol()
    ' Create a Table
    Dim ws As Worksheet
    Dim rg As Range
    Set ws = ActiveSheet
    Set rg = ws.Range("A1").CurrentRegion
    ws.ListObjects.Add(xlSrcRange, rg, , xlYes).Name = "myNonRepeatedTableName"

    ' Filter the created table
    Dim Io As ListObject
    Dim iCol As Long
    ' Set reference to the first Table on the sheet 
    ' That should be the recently created one
    Set lo = Sheets("Totalinfo").ListObjects(1)
    ' Set filter field
    iCol = lo.ListColumns("yourColumnNameToFilter").Index
    ' Non-blank cells – use NOT operator <>
    lo.Range.AutoFilter Field:=iCol, Criteria1:="<>"
End Sub

answered Jun 12, 2022 at 14:23

This might be completely off base, but can’t you just copy the whole column into a new spreadsheet and then sort the column? I’m assuming that you don’t need to maintain the order integrity.