在Excel中使用VBA选择非空白单元格 [英] Selecting non-blank cells in Excel with VBA

问题描述

我刚刚开始进入VBA，我打了一个路障。

我有一个包含50+列，900+行的工作表数据的。我需要重新格式化大约10个这些列，并将它们粘贴到一个新的工作簿中。

如何以编程方式选择book1列中的每个非空白单元格，运行它通过一些功能，并将结果放在book2中？

解决方案

以下VBA代码应该让你开始。它将原始工作簿中的所有数据复制到一个新的工作簿，但它将为每个值添加1，所有空白单元格将被忽略。

  Option Explicit 
 
 Public Sub exportDataToNewBook（）
 Dim rowIndex As Integer 
 Dim colIndex As Integer 
 Dim dataRange As Range 
 Dim thisBook作为工作簿
 Dim newBook As Workbook 
 Dim newRow As Integer 
 Dim temp 
 
'//设置您的数据范围
设置dataRange = Sheet1.Range（A1：B100）
 
'//创建一个新的工作簿
设置newBook = Excel.Workbooks.Add 
 
'//循环通过book1中的数据，一次一列
对于colIndex = 1到dataRange.Columns.Count 
 newRow = 0 
对于rowIndex = 1到dataRange.Rows.Count 
使用dataRange.Cells（rowIndex，colIndex）
 
'//忽略空单元格
如果.value<> 然后
 newRow = newRow + 1 
 temp = doSomethingWith（.value）
 newBook.ActiveSheet.Cells（newRow，colIndex）.value = temp 
 End If 
 
结束
下一行rowIndex 
下一个colIndex 
 End Sub 
  

 私有函数doSomethingWith（aValue）
 
'//这是在那里你可以计算一个不同的值
'//用于新的工作簿
'//在这个例子中，我只是添加一个。 
 aValue = aValue + 1 
 
 doSomethingWith = aValue 
结束功能
  

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?

解决方案

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

这篇关于在Excel中使用VBA选择非空白单元格的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！