如何不跳过VBA中的空行或单元格? [英] How to NOT skip empty rows or cells in vba?

问题描述

是否有任何方法可以为每个循环(范围为一个循环但包含空单元格/行)执行vba.

Is there any way to do a vba for each loop that loops a range but includes the empty cells/rows.

问题:当我在其他工作表的行上进行复制时，循环会自动跳过"A"列中空白的单元格，并完全跳过整行，因此我丢失了该行中的一些数据，因为A栏为空.

PROBLEM: When I do a copy on the row from some other worksheet, the loop automatically skips the cells in the "A" column that are blank and skips that entire row completely and therefore I lose some data in that row because ONLY the A column is blank.

例如

 For Each cell In wks.Range("A2", "A1000")
    cell.EntireRow.Copy Destination:=Worksheets("Master").Range("A65536").End(xlUp).Offset(1, 0)     
 Next cell

推荐答案

我相信问题在于您正在使用的End()方法中使用了偏移量选择.当单元格为空时，这样的扩展范围选择将停止.尝试像这样显式指定所需的范围行.

I believe the problem is in the use of the offset selection in the End() method you're using. Extending a range selection like that stops when cells are empty. Try specifying your desired range rows explicitly like this.

ThisWorkbook.ActiveSheet.Range("E10:E20").Copy _
  ThisWorkbook.ActiveSheet.Range("F10:F20")

当然，对col和row进行硬编码将无法满足您的情况，您将要使用wks变量而不是activesheet，而只需将row和col的字母和数字替换为正确的值通过串联.可能更接近此:

Of course hardcoding the col and row won't work for your situation and you're going to want to use your wks variable rather than the activesheet, but just replace the letter and number of the row and col with the correct values by concatenating. Something probably closer to this:

Dim fromCol as String, toCol as String, fromRow as String, toRow as String
' populate your 4 variables through your own logic here 

    wks.Range(fromCol & fromRow ":" & fromCol & toRow).Copy _
      wks.Range(toCol & fromRow & ":" & toCol & toRow)

祝你好运！

这篇关于如何不跳过VBA中的空行或单元格?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！