Selecting non-blank cells in Excel with VBA

Question

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

User · Accepted Answer

The following VBA code should get you started. It will copy all of the data in the original workbook to a new workbook, but it will have added 1 to each value, and all blank cells will have been ignored.

Option Explicit

Public Sub exportDataToNewBook()
    Dim rowIndex As Integer
    Dim colIndex As Integer
    Dim dataRange As Range
    Dim thisBook As Workbook
    Dim newBook As Workbook
    Dim newRow As Integer
    Dim temp

    '// set your data range here
    Set dataRange = Sheet1.Range("A1:B100")

    '// create a new workbook
    Set newBook = Excel.Workbooks.Add

    '// loop through the data in book1, one column at a time
    For colIndex = 1 To dataRange.Columns.Count
        newRow = 0
        For rowIndex = 1 To dataRange.Rows.Count
            With dataRange.Cells(rowIndex, colIndex)

            '// ignore empty cells
            If .value <> "" Then
                newRow = newRow + 1
                temp = doSomethingWith(.value)
                newBook.ActiveSheet.Cells(newRow, colIndex).value = temp
                End If

            End With
        Next rowIndex
    Next colIndex
End Sub

Private Function doSomethingWith(aValue)

    '// This is where you would compute a different value
    '// for use in the new workbook
    '// In this example, I simply add one to it.
    aValue = aValue + 1

    doSomethingWith = aValue
End Function

User · Answer

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

User · Answer

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

User · Answer

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

[vba] Selecting non-blank cells in Excel with VBA

Examples related to vba

Examples related to excel

Examples related to spreadsheet