enter image description here enter image description here

We have: a large number of tables in Excel format

What is required: automatically work with the values ​​in the table.

Example: A1 example

1) The number of rows is unknown, that is, the end of the work range is the end of the table. It is necessary, for example, from B2 to B (last_string) to put the value 0.3 (0.3 = const, does not change). You can navigate by A, i.e. we look at the coordinates of the last record A , and the same number of rows will have a column B.

2) C2: D (last_string) delete values, leave empty.

enter image description here

In the end, it should be like this:

(xxxx is the end of the file, that is, in fact, there is no such line, I just marked the end of the file like this.)

Screen 3: Missing values ​​- end of file. That is, in practice, A / B / C / D: 11 - no, it remains empty and is not affected in any way.

How should it look right?

  • friend - vba processes 20,000 files with strings and replacing them or viewing them in 5 minutes ... Set the task correctly - and they will help you ... In your question - complete rubbish ... Replace the leg with a handle in line N and move the buckets to point A HELP !!!! I myself understood what I wrote ??? - SergeyE
  • How to put it correctly? 1) The beginning of T2 and up to Tx, x is the last line of the document. All this set the value of 0.3. 2) The beginning is U2 and the end of ARx, ie it turns out that x rows are selected, and 24 columns. All existing values ​​are deleted, leaving empty. - PaTRoN_iX
  • then decide if you understand everything !!! what is the problem ??? - SergeyE
  • that's better? Added an example - PaTRoN_iX
  • nRow = sh.Cells (sh.Rows.Count, 1) .End (xlUp) .Row 'in the first column the last non-empty row, then what? - SergeyE

2 answers 2

Batch file processing. Macros should be placed in the general module of any book.

Next to this book to place the folder files2000 , in which to place all the files intended for processing. The path to the folder and its name can be selected by changing the line of code.

sPath = ThisWorkbook.Path & "\files2000\"

Ranges are set in the lines

 .Range("B2:B" & lRw).Value = dValue .Range("C2:D" & lRw).ClearContents

The value to add to the range of column B is given by a constant. You can without it - write the value in the string

 .Range("B2:B" & lRw).Value = 0.3

Macro

 Sub DataChange() Dim wBook As Workbook Dim sPath As String Dim sFName As String Dim lRw As Long Const dValue As Double = 0.3 With Application: .ScreenUpdating = False: .DisplayAlerts = False: End With sPath = ThisWorkbook.Path & "\files2000\" sFName = Dir(sPath & "*.xls*", vbDirectory) Do While sFName <> "" Set wBook = Workbooks.Open(Filename:=sPath & sFName) With wBook With .Worksheets(1) lRw = .Cells(.Rows.Count, 1).End(xlUp).Row If lRw > 1 Then .Range("B2:B" & lRw).Value = dValue .Range("C2:D" & lRw).ClearContents wBook.Save End If End With .Close End With sFName = Dir Loop With Application: .ScreenUpdating = True: .DisplayAlerts = True: End With MsgBox "OK", 64, "" End Sub

Definition of the last line

 .Cells(.Rows.Count, 1).End(xlUp).Row - последняя видимая заполненная ячейка столбца А

All lines must be expanded in the files being processed (filters removed, if any), otherwise the range of lines may be determined incorrectly.

Find the lower limit by the size of the custom range (we assume that row 1 contains the table header):

 lRw = .UsedRange.Rows.Count

The disadvantage of this option is that all formatted strings will fall into the range, including those without data (this is often found with incompetent copying: 10 lines are filled, and the range is by a million).

The range of filled lines relative to the cell:

 lRw = .Range("A1").CurrentRegion.Rows.Count

At the same time, there must be confidence that the data range is inseparable (data is not separated by empty lines and columns).

     Sub Очистить_лист() nRow = Лист1.Cells(Лист1.Rows.Count, 1).End(xlUp).Row 'в первом столбце последняя не пустая строка nCol = Лист1.Cells.SpecialCells(xlCellTypeLastCell).Column ' определили номер последнеи колонки с хоть одной заполненнои ячеикои For i = 2 To nRow Лист1.Cells(i, 20).FormulaR1C1 = "0,3" Лист1.Range(Cells(i, 21), Cells(i, nCol)).ClearContents Next End Sub

    enter image description here

    • Sheet1.Range ("A3: A5"). ClearContents Make a Macro Record — Perform Range Cleaning and Read the Macro - SergeyE
    • I did not understand anything at all. The problem is not solved. But thanks for your efforts. (nRow = sh.Cells (sh.Rows.Count, 1) .End (xlUp) .Row For i = 2 To nRow sh.Cells (i, 2) .FormulaR1C1 = "0.3" Application.Goto Reference: = "Òåñò1" Application.WindowState = xlNormal Application.Goto Reference: = "Òåñò1" End Sub) - PaTRoN_iX
    • Friend - you find the last row in the column and from row 2 to the last for column 3 you put the values, and from column 4 to column 24 you clear the values. Terms of the problem throw in the topic - get an answer - SergeyE
    • Maybe I will throw off 2 real files, that is, with which the real work is happening. One - before manual replacement, the second with manual replacement. Maybe it will be so clearer? I can even 4, so that it is clear that each time the number of lines is different. But, in all columns the number of rows is the same. - PaTRoN_iX
    • There are no problems for kiddies "that each time the number of rows is different. But, in all columns the number of rows is the same" - SergeyE