VBA Excel : Boolean loop to check last full column

[Noone]

Hello everyone,

Hope you are all doing good.

So I am working on a macro, the goal is to copy and paste an x cell of the last column added, knowing that each day a column is added till the Column "AX" is reached .


So the idea I had, is to check if the last column (AX) is empty, if yes then I move left with 3 columns, then do again the same thing if it's empty move another time with 3 columns to the left, untill It finds a full cell, then do the copy and paste process. (x = x - 3 )


The problem Is that I don't know how to define the loop to keep doing the check untill it finds a full cell.

I already created a random code but it s not complete and doesn't work.

Sub Test_FM()
Application.ScreenUpdating = False
x = 0
Sheets("Test FM").Select
Range("AX9").Select
If ActiveCell.Value = "" Then
Range("AX9").Activate
ActiveCell.Offset(rowoffset:=0, columnoffset:=x).Activate
x = x - 3
Range("AX9").Select
ActiveCell.Offset(rowoffset:=x, columnoffset:=0).Activate
ActiveSheet.Rows(ActiveCell.Row).Select
Range("AX9").Select
Range("AX9").Activate
ActiveCell.Offset(rowoffset:=x, columnoffset:=0).Activate
Selection.Copy
Sheets("DR 02").Select
Range("M19").Select
Selection.PasteSpecial


ElseIf 
IsEmpty(ActiveCell) = False Then
Sheets("Test FM").Select
Range("AX9").Select
Range("AX9").Activate
ActiveCell.Offset(rowoffset:=x, columnoffset:=0).Activate
Selection.Copy
Sheets("DR 02").Select
Range("M19").Select
Selection.PasteSpecial
End IF
End Sub

Thank you

[SamT]

Sub SamT_VBAX()
'Copies cell in Row 6 above last used cell in Row 9. That is what your code appears to do.
'Assumes "AX9" is last possible used cell in Row 9. 

Dim Cel As Range

'Start at last possible used cell
Set Cel = Sheets("Test FM").Range("AX9")

'Use actual last cell
If Cel = "" Then Set Cel = Cel.End(xlToLeft).

'Copy cell in Row 6 above last used cell
Cel.Offset(-3) Copy Sheets("DR 02").Range("M19")
End Sub

[Artik]

If it can be assumed that cell AY9 will always be empty, the code can be shortened to form:

Sub Test_FM_1()

    Worksheets("Test FM").Range("AY9").End(xlToLeft).Offset(-3).Copy Worksheets("DR 02").Range("M19")
End Sub

Artik

[Noone]

Hello Guys,

Thank you for your answers.

That is a good part to start with, but how can I create the loop so it can keep checking from a column to column - 4 untill it finds the full case in row 9 of the last full column ?

The thing is about repeating the process untill it finds that columns, in both codes above, It will check once that s all, xhat I really want is to check AX9 if it is full then do the copy paste, if it's empty go to case AT9 then check again if it is full do the cope paste if it is epmty go to case AP9 ...

Hope I made it clear, cause I am not good at expressing what I want, I am sorry for that.

thanks again for your help

[SamT]

Why loop? Is actual looping a requirement of your teacher? Is AU9 or AV9 not empty?

.End(xlToTeft) will reach the last non empty cell in a Row.

OK, to provide EXACTLY what you asked for

Dim Cel As Range
Set Cel = Range("AX9")
Do While Cel = ""
   Set Cel = Cel.Offset(-3)
Loop
'Cel is now the last (by 3's) non empty cell, even if the other cells (AU9, AV9, etc) are also not empty

What to copy is not clear... Cell in Row 9 or cell in Row 6?

nb: "Cells" is a VBA Keyword, "Cel" is not, I never use "Cell" as a variable because I believe that Microsoft might decide to make it a Keyword. I always use "Cel" as a variable name.

[snb]

L'information que tu as donné n'est pas complète, je crains.
La structure de la page n'est pas claire.
The last column:

Sub M_snb()
   msgbox sheet1.usedrange.columns.count
End Sub

[Artik]

snb, You've taken the shortcut too much. Rather

Sub M_snb_1()
   With Sheet1.UsedRange
    MsgBox .Columns.Count + .Column - 1
  End With
End Sub

And that doesn't have to tell the truth. But I know you are aware of this.

Artik

[Noone]

Hello everyone,

Sorry for this late reply I have been working today.

I am really thankful for all of you guys, I really mean it.

I will try to use all of the elements you gave me guys and I will update you.

Also tomorrow or in the next days I will try to prepare an exemple of the sheet I am working on, and post it here, so if anyone want to have a close look on it and understand better the problem, It will be helpful.

Again thank you all guys.

Best regards,