Home

VBA code to start auto fill in column A until multiple or single data available in column B.

%3CLINGO-SUB%20id%3D%22lingo-sub-1184194%22%20slang%3D%22en-US%22%3EVBA%20code%20to%20start%20auto%20fill%20in%20column%20A%20until%20multiple%20or%20single%20data%20available%20in%20column%20B.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1184194%22%20slang%3D%22en-US%22%3E%3CP%3EDear%20Reader%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20work%20on%20some%20handful%20of%20excels%20everyday.%20For%20some%20files%20I%20need%20to%20fill%20Column%20A%20until%20multiple%20or%20single%20data%20available%20in%20Column%20B(usually%20from%20B2%20the%20datas%20will%20be%20available%20as%20B1%20could%20be%20a%20header%20row).%20and%20if%20no%20data%20available%20in%20a%20sheet%20the%20macro%20should%20move%20for%20the%20next%20file%20without%20throwing%20any%20error.%20what%20would%20be%20the%20code%20for%20the%20same.%20got%20failed%20with%20multiple%20codes.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20in%20advance%20for%20your%20assistance.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1184194%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1184221%22%20slang%3D%22en-US%22%3ERe%3A%20VBA%20code%20to%20start%20auto%20fill%20in%20column%20A%20until%20multiple%20or%20single%20data%20available%20in%20column%20B.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1184221%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F557800%22%20target%3D%22_blank%22%3E%40Chalz2709%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAre%20you%20saying%20that%20one%20or%20multiple%20excel%20files%20are%20opened%20and%20you%20want%20to%20fill%20column%20A%20based%20on%20what%20is%20filled%20in%20column%20B%3F%3C%2FP%3E%3CP%3EWhat%20is%20to%20be%20filled%20in%20column%20A%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIsn't%20it%20better%20if%20you%20upload%20a%20sample%20file%20to%20show%20us%20what%20exactly%20you%20are%20trying%20to%20achieve%20by%20mocking%20up%20the%20desired%20output%20manually%20in%20column%20A%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1186283%22%20slang%3D%22en-US%22%3ERe%3A%20VBA%20code%20to%20start%20auto%20fill%20in%20column%20A%20until%20multiple%20or%20single%20data%20available%20in%20column%20B.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1186283%22%20slang%3D%22en-US%22%3EHello%20Sir%2C%3CBR%20%2F%3EI%20want%20to%20update%20on%20multiple%20excels.%20Yes%20Column%20A%20is%20to%20be%20auto%20filled%20with%20A2%20Data%20until%20the%20datas%20in%20column%20b%20is%20available.%3CBR%20%2F%3E%3CBR%20%2F%3EFile%20vairies%20everyday.%20sometimes%20multiple%20number%20of%20rows%20would%20be%20filled.%20Sometimes%20single%20and%20later%20sometimes%20empty%20sheets.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1186317%22%20slang%3D%22en-US%22%3ERe%3A%20VBA%20code%20to%20start%20auto%20fill%20in%20column%20A%20until%20multiple%20or%20single%20data%20available%20in%20column%20B.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1186317%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F557800%22%20target%3D%22_blank%22%3E%40Chalz2709%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20following%20code%20will%20loop%20through%20all%20the%20worksheets%20of%20the%20open%20workbooks%20and%20fill%20the%20column%20A%20with%20what%20is%20there%20in%20cell%20A2%20on%20those%20sheets.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3ESub%20FillColumnA()%0ADim%20wb%20As%20Workbook%0ADim%20ws%20As%20Worksheet%0ADim%20lr%20As%20Long%0A%0AApplication.ScreenUpdating%20%3D%20False%0A%0AFor%20Each%20wb%20In%20Application.Workbooks%0A%20%20%20%20For%20Each%20ws%20In%20wb.Worksheets%0A%20%20%20%20%20%20%20%20lr%20%3D%20ws.Cells(Rows.Count%2C%20%22B%22).End(xlUp).Row%0A%20%20%20%20%20%20%20%20ws.Range(%22A2%3AA%22%20%26amp%3B%20lr).Value%20%3D%20ws.Range(%22A2%22).Value%0A%20%20%20%20Next%20ws%0ANext%20wb%0AApplication.ScreenUpdating%20%3D%20True%0AEnd%20Sub%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E
Highlighted
New Contributor

Dear Reader,

 

I work on some handful of excels everyday. For some files I need to fill Column A until multiple or single data available in Column B(usually from B2 the datas will be available as B1 could be a header row). and if no data available in a sheet the macro should move for the next file without throwing any error. what would be the code for the same. got failed with multiple codes.

 

Thanks in advance for your assistance.

3 Replies
Highlighted

@Chalz2709 

Are you saying that one or multiple excel files are opened and you want to fill column A based on what is filled in column B?

What is to be filled in column A?

 

Isn't it better if you upload a sample file to show us what exactly you are trying to achieve by mocking up the desired output manually in column A?

Highlighted
Hello Sir,
I want to update on multiple excels. Yes Column A is to be auto filled with A2 Data until the datas in column b is available.

File vairies everyday. sometimes multiple number of rows would be filled. Sometimes single and later sometimes empty sheets.
Highlighted

@Chalz2709 

The following code will loop through all the worksheets of the open workbooks and fill the column A with what is there in cell A2 on those sheets.

 

Sub FillColumnA()
Dim wb As Workbook
Dim ws As Worksheet
Dim lr As Long

Application.ScreenUpdating = False

For Each wb In Application.Workbooks
    For Each ws In wb.Worksheets
        lr = ws.Cells(Rows.Count, "B").End(xlUp).Row
        ws.Range("A2:A" & lr).Value = ws.Range("A2").Value
    Next ws
Next wb
Application.ScreenUpdating = True
End Sub