VBA Selection of non-empty cells.
Solved
2000Kennedy
Posted messages
44
Status
Member
-
2000Kennedy Posted messages 44 Status Member -
2000Kennedy Posted messages 44 Status Member -
Hello,
I have been working with VBA for a week now, so my level is not very high, and I'm having some difficulties... I have spent a lot of time searching for a solution, and not finding one, here I am :x.
I have a certain number of filled cells in my column A, and I would simply like to select the non-empty cells and paste them into another Excel sheet.
Knowing that I have a variable number of Excel sheets with a column A that I would like to import into a single sheet.
Here is what I have done. Of course, my function does not work as I want it to; it selects the cells one by one without pasting them.
Sub test(Nb_Sheet)
For i = 1 To Nb_Sheet
Sheets(i).Activate
Range("A4").Select
Do While Not (IsEmpty(ActiveCell))
NbLine = NbLine + 1
Selection.Offset(1, 0).Select
Loop
Application.CutCopyMode = False
Selection.Copy
Sheets("GCE_Globale_target").Select
Range("B4").Select
ActiveSheet.Paste
Next i
How can I correctly implement this application so that each data from the columns of each sheet is pasted into a unique column of a single sheet without overwriting the previous data? :x
Thank you :)
Configuration: Windows XP / Internet Explorer 6.0
I have been working with VBA for a week now, so my level is not very high, and I'm having some difficulties... I have spent a lot of time searching for a solution, and not finding one, here I am :x.
I have a certain number of filled cells in my column A, and I would simply like to select the non-empty cells and paste them into another Excel sheet.
Knowing that I have a variable number of Excel sheets with a column A that I would like to import into a single sheet.
Here is what I have done. Of course, my function does not work as I want it to; it selects the cells one by one without pasting them.
Sub test(Nb_Sheet)
For i = 1 To Nb_Sheet
Sheets(i).Activate
Range("A4").Select
Do While Not (IsEmpty(ActiveCell))
NbLine = NbLine + 1
Selection.Offset(1, 0).Select
Loop
Application.CutCopyMode = False
Selection.Copy
Sheets("GCE_Globale_target").Select
Range("B4").Select
ActiveSheet.Paste
Next i
How can I correctly implement this application so that each data from the columns of each sheet is pasted into a unique column of a single sheet without overwriting the previous data? :x
Thank you :)
Configuration: Windows XP / Internet Explorer 6.0
4 answers
Hello,
There's not much left for you... The syntax to say: "first empty cell of the column."
The last filled cell of column A:
So:
1- Your range to copy (on each sheet) is:
2- The cell where you need to paste your data is:
Knowing that you need to:
- avoid ".Select"
- include a test in the loop to "skip" the sheet GCE_Globale_cible...
Your code (annotated) then becomes:
Edit: code modified following the response from 2000kennedy
Best regards,
-- Every problem has its solution. If there is no solution, where is the problem? --
There's not much left for you... The syntax to say: "first empty cell of the column."
The last filled cell of column A:
Dim DerniereLigne As Integer DerniereLigne = Range("A65536").End(xlUp).Row So:
1- Your range to copy (on each sheet) is:
Dim DerniereLigne As Integer DerniereLigne = Range("A65536").End(xlUp).Row Range("A4:A" & DerniereLigne).Copy 2- The cell where you need to paste your data is:
Sheets("GCE_Globale_cible").Range("B65536").End(xlUp).Offset(1, 0).Row Knowing that you need to:
- avoid ".Select"
- include a test in the loop to "skip" the sheet GCE_Globale_cible...
Your code (annotated) then becomes:
Sub test() 'Variable declaration 'Here we designate Ws to the different sheets of the workbook Dim Ws As Worksheet Dim DerniereLigne As Integer 'For all sheets in the active workbook For Each Ws In ThisWorkbook.Worksheets 'Do nothing if the sheet name is "GCE_Globale_cible" If Ws.Name = "GCE_Globale_cible" Then 'otherwise, if the sheet name is different from "GCE_Globale_cible" Else 'determine the last filled row DerniereLigne = Ws.Range("A65536").End(xlUp).Row 'copy the range from A4 to the last row and paste it 'into the first empty cell in column B of the sheet "GCE_Globale_cible" Ws.Range("A4:A" & DerniereLigne).Copy Sheets("GCE_Globale_cible").Range("B65536").End(xlUp).Offset(1, 0) End If 'Next sheet please... Next Ws End Sub Edit: code modified following the response from 2000kennedy
Best regards,
-- Every problem has its solution. If there is no solution, where is the problem? --
Hello,
Try this:
;o)
--
“What is well conceived is clearly expressed, and the words to say it come easily.”
Nicolas Boileau
Try this:
Sub test(Nb_Sheet) Dim LastRow As Long Dim i As Long Dim j As Long Dim row As Long row = 4 For i = 1 To Nb_Sheet LastRow = Sheets(i).Range("A65536").End(xlUp).Row For j = 4 to LastRow If Sheets(i).Range("A" & j).Value <> "" Then Sheets("GCE_Globale_target").Range("B" & row).Value = Sheets(i).Range("A" & j).Value row = row + 1 End If Next j Next i End Sub ;o)
--
“What is well conceived is clearly expressed, and the words to say it come easily.”
Nicolas Boileau
Thank you both for your answers ;)
It works now.
However, Pijaku, I have a 1004 error that displays for the line
Range("A4:A" & DerniereLigne).Copy Sheets("GCE_Globale_cible").Range("B65536").End(xlUp).Offset(1, 0).Row
Do I need to declare something in my main function?
It works now.
However, Pijaku, I have a 1004 error that displays for the line
Range("A4:A" & DerniereLigne).Copy Sheets("GCE_Globale_cible").Range("B65536").End(xlUp).Offset(1, 0).Row
Do I need to declare something in my main function?
Eh oui!!! That's what happens when you don't test... Sorry... here's the corrected code: (in bold the changes...)
Sub test() 'Declaration of variables 'Here we will designate Ws for the different sheets in the workbook Dim Ws As Worksheet Dim DerniereLigne As Integer 'For all the sheets in the active workbook For Each Ws In ThisWorkbook.Worksheets 'Do nothing if the sheet name is "GCE_Globale_cible" If Ws.Name = "GCE_Globale_cible" Then 'otherwise, if the sheet name is different from "GCE_Globale_cible" Else 'we determine the last filled line DerniereLigne = Ws.Range("A65536").End(xlUp).Row 'we copy the range from A4 to the last line and paste it 'into the first empty cell in col B of the sheet "GCE_Globale_cible" Ws.Range("A4:A" & DerniereLigne).Copy Sheets("GCE_Globale_cible").Range("B65536").End(xlUp).Offset(1, 0) 'here I removed .Row attention error... End If 'Next sheet please... Next Ws End Sub
Thank you very much :)
I'm sorry to bother you a little more :x
Let's imagine, which is the case with my new issue...
what I have:-----------------------------and what I want to obtain:
A -------------------------------------------------------A
A--------------------------------------------------------B
A -------------------------------------------------------C
A--------------------------------------------------------
B--------------------------------------------------------
B--------------------------------------------------------
B--------------------------------------------------------
B--------------------------------------------------------
C
C
C
Actually, here's the thing... I thought that what you explained to me, I could apply to my Excel table. Here is my complete problem:
I have a table in Excel that has 2 different pieces of information in the columns: provenance and date. In 2 rows, I have the product category and the product type, meaning that each category is divided into 3 types of products. I would like to retrieve all this information on one line, with each column corresponding to provenance, date, product category, and product type respectively. The goal is to facilitate reading when transferring data to my database.
Of course, some values are linked to the original table, and I would like to find them in the resulting table.
------------------------ Prod cat 1-----------------Prod cat 2
provenance-date---type1----type2-type3----------type1--type2-type3
france---------jan------3--------1-----2 ----------------------2------1------3
france---------fev------1--------5-----0 -----------------------8-----2------1
espagne------jan-----x---------x---- x-----------------------x-----x-------x
espagne------fev -----x---------x----x-----------------------x-----x------x
This is the starting table and I would like:
provenance---prod cat---type---jan--fev
france--------Prod cat1-type1---3----1
france--------Prod cat1-type2---1----5
france--------Prod cat1-type3---2----0
france--------Prod cat2-type1---2----8
etc------------etc----------etc
I may not be expressing myself well :x But is there a VBA function or an Excel manipulation that would allow me to transform table 1 into the form of table 2, knowing that the data is linked?
I'm sorry to bother you a little more :x
Let's imagine, which is the case with my new issue...
what I have:-----------------------------and what I want to obtain:
A -------------------------------------------------------A
A--------------------------------------------------------B
A -------------------------------------------------------C
A--------------------------------------------------------
B--------------------------------------------------------
B--------------------------------------------------------
B--------------------------------------------------------
B--------------------------------------------------------
C
C
C
Actually, here's the thing... I thought that what you explained to me, I could apply to my Excel table. Here is my complete problem:
I have a table in Excel that has 2 different pieces of information in the columns: provenance and date. In 2 rows, I have the product category and the product type, meaning that each category is divided into 3 types of products. I would like to retrieve all this information on one line, with each column corresponding to provenance, date, product category, and product type respectively. The goal is to facilitate reading when transferring data to my database.
Of course, some values are linked to the original table, and I would like to find them in the resulting table.
------------------------ Prod cat 1-----------------Prod cat 2
provenance-date---type1----type2-type3----------type1--type2-type3
france---------jan------3--------1-----2 ----------------------2------1------3
france---------fev------1--------5-----0 -----------------------8-----2------1
espagne------jan-----x---------x---- x-----------------------x-----x-------x
espagne------fev -----x---------x----x-----------------------x-----x------x
This is the starting table and I would like:
provenance---prod cat---type---jan--fev
france--------Prod cat1-type1---3----1
france--------Prod cat1-type2---1----5
france--------Prod cat1-type3---2----0
france--------Prod cat2-type1---2----8
etc------------etc----------etc
I may not be expressing myself well :x But is there a VBA function or an Excel manipulation that would allow me to transform table 1 into the form of table 2, knowing that the data is linked?