Excel 操作，具有十分明显的针对性，故很难通用，但这并不妨碍参考后以解决自己的实际问题。
有一汇总表如下：
当然，只是示范，产品的代码应该唯一！现在要根据此汇总表产生各个客户的产品清单。由于客户较多，汇总分表1，表2；客户清单模板根据产品类别，每个清单又分为三个表。做个模板，然后手工复制粘贴，完成需要小半天的时间。还是写个程序来帮帮忙吧。
首先，是下载 NPOI 库及参考手册，花个10来分钟粗略看看，然后开工。主要代码如下：
<Window x:Class="x01.ExcelHelper.SplitWindow" xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation" xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml" WindowStartupLocation="CenterScreen" Title="x01.SplitWindow" Height="310" Width="480"> <Window.Resources> <Style TargetType="TextBox" x:Key="ShortBoxKey"> <Setter Property="MinWidth" Value="20" /> <Setter Property="Margin" Value="5" /> </Style> <Style TargetType="TextBox"> <Setter Property="Height" Value="20" /> </Style> <Style TargetType="Button"> <Setter Property="Margin" Value="5" /> <Setter Property="Height" Value="20" /> </Style> <Style TargetType="TextBlock"> <Setter Property="Margin" Value="0 5 0 0" /> <Setter Property="Height" Value="20" /> </Style> </Window.Resources> <Grid Margin="5 10 5 5"> <Grid.RowDefinitions> <RowDefinition Height="Auto" /> <RowDefinition Height="Auto" /> <RowDefinition Height="Auto" /> <RowDefinition Height="Auto" /> <RowDefinition Height="Auto" /> </Grid.RowDefinitions> <Grid.ColumnDefinitions> <ColumnDefinition Width="Auto" /> <ColumnDefinition Width="*" /> <ColumnDefinition Width="Auto" /> </Grid.ColumnDefinitions> <TextBlock Grid.Row="0" Grid.Column="0" HorizontalAlignment="Right">原始文件:</TextBlock> <TextBox Grid.Row="0" Grid.Column="1" Name="tbxOriginPath" /> <Button Grid.Row="0" Grid.Column="2" Width="30" Name="OpenOriginButton" Click="OpenOriginButton_Click">…</Button> <StackPanel Grid.Row="1" Grid.ColumnSpan="3"> <StackPanel Orientation="Horizontal"> <TextBlock>原始表1: 表名</TextBlock> <TextBox Name="tbxOriginSheet1Name" MinWidth="40" Margin="5 0" /> <TextBlock>起始行</TextBlock> <TextBox Name="tbxOriginSheet1StartRow" Style="{StaticResource ShortBoxKey}" /> <TextBlock>结束行</TextBlock> <TextBox Name="tbxOriginSheet1EndRow" Style="{StaticResource ShortBoxKey}" /> <TextBlock>起始列</TextBlock> <TextBox Name="tbxOriginSheet1StartCol" Style="{StaticResource ShortBoxKey}" /> <TextBlock>结束列</TextBlock> <TextBox Name="tbxOriginSheet1EndCol" Style="{StaticResource ShortBoxKey}" /> <TextBlock>代码列</TextBlock> <TextBox Name="tbxOriginSheet1CodeCol" Style="{StaticResource ShortBoxKey}" /> </StackPanel> <StackPanel Orientation="Horizontal"> <TextBlock>原始表2: 表名</TextBlock> <TextBox Name="tbxOriginSheet2Name" MinWidth="40" Margin="5 0" /> <TextBlock>起始行</TextBlock> <TextBox Name="tbxOriginSheet2StartRow" Style="{StaticResource ShortBoxKey}" /> <TextBlock>结束行</TextBlock> <TextBox Name="tbxOriginSheet2EndRow" Style="{StaticResource ShortBoxKey}" /> <TextBlock>起始列</TextBlock> <TextBox Name="tbxOriginSheet2StartCol" Style="{StaticResource ShortBoxKey}" /> <TextBlock>结束列</TextBlock> <TextBox Name="tbxOriginSheet2EndCol" Style="{StaticResource ShortBoxKey}" /> <TextBlock>代码列</TextBlock> <TextBox Name="tbxOriginSheet2CodeCol" Style="{StaticResource ShortBoxKey}" /> </StackPanel> </StackPanel> <TextBlock Grid.Row="2" Grid.Column="0" HorizontalAlignment="Right">模板文件:</TextBlock> <TextBox Grid.Row="2" Grid.Column="1" Name="tbxTemplatePath" /> <Button Grid.Row="2" Grid.Column="2" Width="30" Name="OpenTemplateButton" Click="OpenTemplateButton_Click">…</Button> <StackPanel Grid.Row="3" Grid.ColumnSpan="3"> <StackPanel Orientation="Horizontal"> <TextBlock>模板表1: 表名</TextBlock> <TextBox Name="tbxTemplateSheet1Name" MinWidth="40" Margin="5 0" /> <TextBlock>起始行</TextBlock> <TextBox Name="tbxTemplateSheet1StartRow" Style="{StaticResource ShortBoxKey}" /> <TextBlock>结束行</TextBlock> <TextBox Name="tbxTemplateSheet1EndRow" Style="{StaticResource ShortBoxKey}" /> <TextBlock>起始列</TextBlock> <TextBox Name="tbxTemplateSheet1StartCol" Style="{StaticResource ShortBoxKey}" /> <TextBlock>结束列</TextBlock> <TextBox Name="tbxTemplateSheet1EndCol" Style="{StaticResource ShortBoxKey}" /> <TextBlock>代码列</TextBlock> <TextBox Name="tbxTemplateSheet1CodeCol" Style="{StaticResource ShortBoxKey}" /> </StackPanel> <StackPanel Orientation="Horizontal"> <TextBlock>模板表2: 表名</TextBlock> <TextBox Name="tbxTemplateSheet2Name" MinWidth="40" Margin="5 0" /> <TextBlock>起始行</TextBlock> <TextBox Name="tbxTemplateSheet2StartRow" Style="{StaticResource ShortBoxKey}" /> <TextBlock>结束行</TextBlock> <TextBox Name="tbxTemplateSheet2EndRow" Style="{StaticResource ShortBoxKey}" /> <TextBlock>起始列</TextBlock> <TextBox Name="tbxTemplateSheet2StartCol" Style="{StaticResource ShortBoxKey}" /> <TextBlock>结束列</TextBlock> <TextBox Name="tbxTemplateSheet2EndCol" Style="{StaticResource ShortBoxKey}" /> <TextBlock>代码列</TextBlock> <TextBox Name="tbxTemplateSheet2CodeCol" Style="{StaticResource ShortBoxKey}" /> </StackPanel> <StackPanel Orientation="Horizontal"> <TextBlock>模板表3: 表名</TextBlock> <TextBox Name="tbxTemplateSheet3Name" MinWidth="40" Margin="5 0" /> <TextBlock>起始行</TextBlock> <TextBox Name="tbxTemplateSheet3StartRow" Style="{StaticResource ShortBoxKey}" /> <TextBlock>结束行</TextBlock> <TextBox Name="tbxTemplateSheet3EndRow" Style="{StaticResource ShortBoxKey}" /> <TextBlock>起始列</TextBlock> <TextBox Name="tbxTemplateSheet3StartCol" Style="{StaticResource ShortBoxKey}" /> <TextBlock>结束列</TextBlock> <TextBox Name="tbxTemplateSheet3EndCol" Style="{StaticResource ShortBoxKey}" /> <TextBlock>代码列</TextBlock> <TextBox Name="tbxTemplateSheet3CodeCol" Style="{StaticResource ShortBoxKey}" /> </StackPanel> </StackPanel> <Button Grid.Row="4" Grid.ColumnSpan="3" HorizontalAlignment="Right" Name="GenerateFilesButton" Margin="0 10 5 0" Height="32" Click="GenerateFilesButton_Click">_Generate Files</Button> </Grid> </Window>
/** * SplitWindow.cs (c) 2017 by x01 */ using System; using System.Collections.Generic; using System.IO; using System.Text; using System.Windows; using System.Windows.Controls; using System.Windows.Data; using System.Windows.Documents; using System.Windows.Input; using System.Windows.Media; using Microsoft.Win32; using NPOI.HSSF.UserModel; using NPOI.SS.UserModel; namespace x01.ExcelHelper { /// <summary> /// Interaction logic for SplitWindow.xaml /// </summary> public partial class SplitWindow : Window { #region Settings public string OriginPath { get { if (string.IsNullOrEmpty(tbxOriginPath.Text)) throw new Exception("请选择原始文件！"); return tbxOriginPath.Text; } } public string OriginSheet1Name { get { return tbxOriginSheet1Name.Text; } } public int OriginSheet1StartRow { get { int row; if (!int.TryParse(tbxOriginSheet1StartRow.Text, out row)) throw new Exception("请在表1起始行中填充正确的数字！"); return row; } } public int OriginSheet1EndRow { get { int row; if (!int.TryParse(tbxOriginSheet1EndRow.Text, out row)) throw new Exception("请在表1结束行中填入正确的数字！"); return row; } } public int OriginSheet1StartCol { get { int col; if (!int.TryParse(tbxOriginSheet1StartCol.Text, out col)) throw new Exception("请在表1起始列中填入正确的数字！"); return col; } } public int OriginSheet1EndCol { get { int col; if (!int.TryParse(tbxOriginSheet1EndCol.Text, out col)) throw new Exception("请在表1结束列中填入正确的数字！"); return col; } } public int OriginSheet1CodeCol { get { int col; if (!int.TryParse(tbxOriginSheet1CodeCol.Text, out col)) throw new Exception("请在表1代码列中填入正确的数字！"); return col; } } public string OriginSheet2Name { get { return tbxOriginSheet2Name.Text; } } public int OriginSheet2StartRow { get { int row; if (!int.TryParse(tbxOriginSheet2StartRow.Text, out row)) throw new Exception("请在表2起始行中填入正确的数字！"); return row; } } public int OriginSheet2EndRow { get { int row; if (!int.TryParse(tbxOriginSheet2EndRow.Text, out row)) throw new Exception("请在表2结束行中填入正确的数字！"); return row; } } public int OriginSheet2StartCol { get { int col; if (!int.TryParse(tbxOriginSheet2StartCol.Text, out col)) throw new Exception("请在表2开始列中填入正确的数字！"); return col; } } public int OriginSheet2EndCol { get { int col; if (!int.TryParse(tbxOriginSheet2EndCol.Text, out col)) throw new Exception("请在表2结束列中填入正确的数字！"); return col; } } public int OriginSheet2CodeCol { get { int col; if (!int.TryParse(tbxOriginSheet2CodeCol.Text, out col)) throw new Exception("请在表2代码列中填入正确的数字！"); return col; } } public string TemplatePath { get { if (string.IsNullOrEmpty(tbxTemplatePath.Text)) throw new Exception("请选择模板文件！"); return tbxTemplatePath.Text; } } public string TemplateSheet1Name { get { return tbxTemplateSheet1Name.Text; } } public int TemplateSheet1StartRow { get { int row; if (!int.TryParse(tbxTemplateSheet1StartRow.Text, out row)) throw new Exception("请在模板表1开始行中填入正确的数字！"); return row; } } public int TemplateSheet1EndRow { get { int row; if (!int.TryParse(tbxTemplateSheet1EndRow.Text, out row)) throw new Exception("请在模板表1结束行中填入正确的数字！"); return row; } } public int TemplateSheet1StartCol { get { int col; if (!int.TryParse(tbxTemplateSheet1StartCol.Text, out col)) throw new Exception("请在模板表1开始列中填入正确的数字！"); return col; } } public int TemplateSheet1EndCol { get { int col; if (!int.TryParse(tbxTemplateSheet1EndCol.Text, out col)) throw new Exception("请在模板表1结束列中填入正确的数字！"); return col; } } public int TemplateSheet1CodeCol { get { int col; if (!int.TryParse(tbxTemplateSheet1CodeCol.Text, out col)) throw new Exception("请在模板表1代码列中填入正确的数字！"); return col; } } public string TemplateSheet2Name { get { return tbxTemplateSheet2Name.Text; } } public int TemplateSheet2StartRow { get { int row; if (!int.TryParse(tbxTemplateSheet2StartRow.Text, out row)) throw new Exception("请在模板表2开始行中填入正确的数字！"); return row; } } public int TemplateSheet2EndRow { get { int row; if (!int.TryParse(tbxTemplateSheet2EndRow.Text, out row)) throw new Exception("请在模板表2结束行中填入正确的数字！"); return row; } } public int TemplateSheet2StartCol { get { int col; if (!int.TryParse(tbxTemplateSheet2StartCol.Text, out col)) throw new Exception("请在模板表2开始列中填入正确的数字！"); return col; } } public int TemplateSheet2EndCol { get { int col; if (!int.TryParse(tbxTemplateSheet2EndCol.Text, out col)) throw new Exception("请在模板表2结束列中填入正确的数字！"); return col; } } public int TemplateSheet2CodeCol { get { int col; if (!int.TryParse(tbxTemplateSheet2CodeCol.Text, out col)) throw new Exception("请在模板表2代码列中填入正确的数字！"); return col; } } public string TemplateSheet3Name { get { return tbxTemplateSheet3Name.Text; } } public int TemplateSheet3StartRow { get { int row; if (!int.TryParse(tbxTemplateSheet3StartRow.Text, out row)) throw new Exception("请在模板表3开始行中填入正确的数字！"); return row; } } public int TemplateSheet3EndRow { get { int row; if (!int.TryParse(tbxTemplateSheet3EndRow.Text, out row)) throw new Exception("请在模板表3结束行中填入正确的数字！"); return row; } } public int TemplateSheet3StartCol { get { int col; if (!int.TryParse(tbxTemplateSheet3StartCol.Text, out col)) throw new Exception("请在模板表3开始列中填入正确的数字！"); return col; } } public int TemplateSheet3EndCol { get { int col; if (!int.TryParse(tbxTemplateSheet3EndCol.Text, out col)) throw new Exception("请在模板表3结束列中填入正确的数字！"); return col; } } public int TemplateSheet3CodeCol { get { int col; if (!int.TryParse(tbxTemplateSheet3CodeCol.Text, out col)) throw new Exception("请在模板表3代码列中填入正确的数字！"); return col; } } #endregion OpenFileDialog openDialog = new OpenFileDialog(); SaveFileDialog saveDialog = new SaveFileDialog(); public SplitWindow() { InitializeComponent(); openDialog.Filter = "Excel Files(*.xls)|*.xls|All Files(*.*)|*.*"; saveDialog.Filter = "Excel Files(*.xls)|*.xls|All Files(*.*)|*.*"; } void OpenTemplateButton_Click(object sender, RoutedEventArgs e) { if ((bool)openDialog.ShowDialog()) { tbxTemplatePath.Text = openDialog.FileName; } } void OpenOriginButton_Click(object sender, RoutedEventArgs e) { if ((bool)openDialog.ShowDialog()) { tbxOriginPath.Text = openDialog.FileName; } } void GenerateFilesButton_Click(object sender, RoutedEventArgs e) { var orgBook = CreateWorkook(OriginPath); var orgSheet1 = GetSheet(orgBook,OriginSheet1Name); var orgSheet2 = GetSheet(orgBook,OriginSheet2Name); var tempBook = CreateWorkook(TemplatePath); var tempSheet1 = GetSheet(tempBook,TemplateSheet1Name); var tempSheet2 = GetSheet(tempBook,TemplateSheet2Name); var tempSheet3 = GetSheet(tempBook, TemplateSheet3Name); if (orgSheet1 != null) { for (int j = OriginSheet1StartCol - 1; j < OriginSheet1EndCol; j++) { string name = orgSheet1.GetRow(OriginSheet1StartRow-1).GetCell(j).StringCellValue; GenerateTemplateSheet(ref orgSheet1, ref tempSheet1, j, name, OriginSheet1StartRow, OriginSheet1EndRow, OriginSheet1CodeCol, TemplateSheet1StartRow, TemplateSheet1EndRow, TemplateSheet1StartCol, TemplateSheet1EndCol, TemplateSheet1CodeCol); GenerateTemplateSheet(ref orgSheet1, ref tempSheet2, j, name, OriginSheet1StartRow, OriginSheet1EndRow, OriginSheet1CodeCol, TemplateSheet2StartRow, TemplateSheet2EndRow, TemplateSheet2StartCol, TemplateSheet2EndCol, TemplateSheet2CodeCol); GenerateTemplateSheet(ref orgSheet1, ref tempSheet3, j, name, OriginSheet1StartRow, OriginSheet1EndRow, OriginSheet1CodeCol, TemplateSheet3StartRow, TemplateSheet3EndRow, TemplateSheet3StartCol, TemplateSheet3EndCol, TemplateSheet3CodeCol); var fs = new FileStream(Path.Combine(Path.GetDirectoryName(TemplatePath),name+".xls"), FileMode.Create); tempBook.Write(fs); fs.Close(); tempBook = CreateWorkook(TemplatePath); tempSheet1 = GetSheet(tempBook, TemplateSheet1Name); tempSheet2 = GetSheet(tempBook, TemplateSheet2Name); tempSheet3 = GetSheet(tempBook, TemplateSheet3Name); } } if (orgSheet2 != null) { for (int j = OriginSheet2StartCol - 1; j < OriginSheet2EndCol; j++) { string name = orgSheet2.GetRow(OriginSheet1StartRow-1).GetCell(j).StringCellValue; GenerateTemplateSheet(ref orgSheet2, ref tempSheet1, j, name, OriginSheet2StartRow, OriginSheet2EndRow, OriginSheet2CodeCol, TemplateSheet1StartRow, TemplateSheet1EndRow, TemplateSheet1StartCol, TemplateSheet1EndCol, TemplateSheet1CodeCol); GenerateTemplateSheet(ref orgSheet2, ref tempSheet2, j, name, OriginSheet2StartRow, OriginSheet2EndRow, OriginSheet2CodeCol, TemplateSheet2StartRow, TemplateSheet2EndRow, TemplateSheet2StartCol, TemplateSheet2EndCol, TemplateSheet2CodeCol); GenerateTemplateSheet(ref orgSheet2, ref tempSheet3, j, name, OriginSheet2StartRow, OriginSheet2EndRow, OriginSheet2CodeCol, TemplateSheet3StartRow, TemplateSheet3EndRow, TemplateSheet3StartCol, TemplateSheet3EndCol, TemplateSheet3CodeCol); var fs = new FileStream(Path.Combine(Path.GetDirectoryName(TemplatePath),name+".xls"), FileMode.Create); tempBook.Write(fs); fs.Close(); tempBook = CreateWorkook(TemplatePath); tempSheet1 = GetSheet(tempBook, TemplateSheet1Name); tempSheet2 = GetSheet(tempBook, TemplateSheet2Name); tempSheet3 = GetSheet(tempBook, TemplateSheet3Name); } } MessageBox.Show("OK!"); } void GenerateTemplateSheet(ref ISheet orgSheet, ref ISheet tempSheet, int orgCol, string name, int orgStartRow, int orgEndRow, int orgCodeCol, int tempStartRow, int tempEndRow, int tempStartCol, int tempEndCol, int tempCodeCol) { for (int i = orgStartRow; i < orgEndRow; i++) { if (tempSheet != null) { for (int y = tempStartRow - 1; y < tempEndRow; y++) { for (int x = tempStartCol - 1; x < tempEndCol; x++) { if (tempSheet.GetRow(y).GetCell(tempCodeCol - 1).NumericCellValue == orgSheet.GetRow(i).GetCell(orgCodeCol - 1).NumericCellValue) { tempSheet.GetRow(y).GetCell(x).SetCellValue(orgSheet.GetRow(i).GetCell(orgCol).NumericCellValue); } } } } } for (int y = tempStartRow - 1; y < tempEndRow; y++) { if (tempSheet == null) break; tempSheet.GetRow(y).GetCell(tempCodeCol - 1).SetCellType(CellType.Blank); } } HSSFWorkbook CreateWorkook(string path) { var fs = new FileStream(path, FileMode.Open, FileAccess.Read); var book = new HSSFWorkbook(fs); fs.Close(); return book; } ISheet GetSheet(HSSFWorkbook book, string sheetName) { if (string.IsNullOrEmpty(sheetName)) return null; return book.GetSheet(sheetName); } } }
运行填充相关设置后，点击生成按钮，瞬间完成，OK！
源代码下载：x01.ExcelHelper
'======================================================================== ' FillAll (c) 2015 by x01 '------------------------ ' 1.将系统销售汇总 sh1 各学校数字填入相应汇总表 sh2. ' 2.参数说明： ' sh1_r1,sh1_r2: 系统销售汇总开始行，结束行 ' sh1_colCode: 征订代码列 ' sh1_colID: 客户编号列 ' sh1_colNumber: 销售数量列 ' sh2_r1,sh2_r2,sh2_c1,sh2_c2: 汇总表开始行，结束行，开始列，结束列 ' sh2_colCode: 征订代码列 ' sh2_rowID: 汇总表客户编号所在行 ' example: FillAll Sheet1,3,122,3,1,10,Sheet33,5,34,6,42,4,3 '======================================================================== Public Sub FillAll(sh1, sh1_r1, sh1_r2, sh1_colCode, sh1_colID, sh1_colNumber, sh2, sh2_r1, sh2_r2, sh2_c1, sh2_c2, sh2_colCode, sh2_rowID) For i = sh1_r1 To sh1_r2 For j = sh2_r1 To sh2_r2 For k = sh2_c1 To sh2_c2 If sh1.Cells(i, sh1_colID) = sh2.Cells(sh2_rowID, k) Then If sh1.Cells(i, sh1_colCode) = sh2.Cells(j, sh2_colCode) Then sh2.Cells(j, k) = sh1.Cells(i, sh1_colNumber) + sh2.Cells(j, k) End If End If Next Next Next Debug.Print "OK!" End Sub '======================================================================== ' FillCol (c) 2015 by x01 '------------------------ ' 1.如满足条件，则将 sh1 中相应项填充到 sh2 中，汇总专用。 ' 2.条件及操作可根据实际作相应调整。 ' 3.取消注释可进行累加。 ' FillCol Sheet1,1,300,8,12,Sheet2,2,73,2,4 '======================================================================== Public Sub FillCol(sh1, sh1_r1, sh1_r2, sh1_c1, sh1_c2, sh2, sh2_r1, sh2_r2, sh2_c1, sh2_c2) For i = sh1_r1 To sh1_r2 For m = sh2_r1 To sh2_r2 If Application.WorksheetFunction.IsNumber(sh2.Cells(m, sh2_c1)) Then If sh1.Cells(i, sh1_c1) = sh2.Cells(m, sh2_c1) And sh1.Cells(i, sh1_c1) > 0 Then sh2.Cells(m, sh2_c2) = sh1.Cells(i, sh1_c2) + sh2.Cells(m, sh2_c2) End If End If Next Next Debug.Print "OK!" End Sub '================================================================= ' SumCol (c) 2014 by x01 '----------------------- ' 计算指定列的合计数，如数量合计，码洋合计等，用于手工表。 ' ' 参数： ' sheet: 所选的表。 ' col: 指定的列。t1为第7列 ' startRow: 开始的行数。 ' endRow: 结束的行数。 '================================================================= Public Sub SumCol(sheet, col, startRow, endRow) Dim result As Double result = 0# For i = startRow To endRow If Application.WorksheetFunction.IsNumber(sheet.Cells(i, col)) Then If Trim(sheet.Cells(i, 1)) = "合计" Then 'If 23# = sheet.Cells(i, 3) Then result = result + sheet.Cells(i, col) 'End If End If End If Next Debug.Print CStr(result) End Sub '===================================================================== ' SubTotal (c) 2014 by x01 '------------------------- ' 求 (单价 * 册数) 的累加小合计 '===================================================================== Public Sub SubTotal(sh, startRow, endRow, startCol, endCol, priceCol) Dim s As Double outRow = endRow + 2 For c = startCol To endCol sh.Cells(outRow, c) = 0 Next For c = startCol To endCol s = 0 For r = startRow To endRow If Application.WorksheetFunction.IsNumber(sh.Cells(r, c)) Then s = s + sh.Cells(r, c) * sh.Cells(r, priceCol) End If Next sh.Cells(outRow, c) = s Next Debug.Print "OK!" End Sub '================================================================ ' DelRow (c) 2014 by x01 '----------------------- ' 根据条件删除行。 ' sheet: 所选的表。 ' col: 指定的条件列，默认为 0 时删除整行。t1 为第7列 ' startRow: 开始行。 ' endRow: 结束行。 '================================================================ Public Sub DelRow(sheet, col, startRow, endRow) For i = endRow To startRow Step -1 If Application.WorksheetFunction.IsNumber(sheet.Cells(i, col)) And Trim(sheet.Cells(i, 1)) <> "合计" Then '此条件可更改 If 0# = sheet.Cells(i, col) + 0# Then Range("a" & CStr(i) & ":a" & CStr(i)).EntireRow.Delete End If End If Next Debug.Print "OK!" End Sub