Loop in Excel

[Kahn]

I have some Names in the Column A, from A1:A34. In this Range from A1
to A34 some rows are empty. I mean some rows have no name. My
requirement is that every week on Sunday I want my file to move last
name from A34 to A1. After that A1 should move to A2, A2 should move
to A3 and so on respectively. If there is no name in any of the row
that empty row should move as well respectively, regardless it have
the name or not. Could you please help me to fine the solution for
the
above problem. Thanks in advance !!!!

 

[Rich Locus]

Here is a little snippet that should accomplish your goal. Just run it as a
Macro or create a button for it:

Option Explicit

Sub Macro1()
'
' Macro1 Macro
' Macro recorded 5/6/2010 by Logicwurks LLC
'
Range("A1").Select
Selection.EntireRow.Insert
Range("A35").Select
Selection.Cut
Range("A1").Select
ActiveSheet.Paste
End Sub

 

[OssieMac]

Hi Kahn,

I am assuming that you want this code to run automatically when you open the
workbook. Therefore you need to save the date for the last Sunday that the
code is run. For this you need to select a cell in your workbook that is out
of the way of your data and initialize it to May 2 2010 (or 2 May 2010
depending you your regional date format)

The following code goes in ThisWorkbook module. To place it there, Alt/F11
to open the VBA editor and then on the left double click ThisWorkbook in the
project explorer and then copy and paste the code into the VBA editor. You
may need to edit the worksheet name and the range where you are going to save
the last Sunday date that the code is run. (Don't forget to initialize the
date in the cell where the code will save the date of the last Sunday the
code is run.

The code will not run multiple times on the same Sunday and if you forget to
run it on a Sunday, then it will run on the first day after the Sunday that
you open the workbook and set the save date to the previous Sunday.

Private Sub Workbook_Open()
Dim dateToday As Date
Dim rngSaveDate As Range
dateToday = Date

'Edit following line to the sheet name and
'range where you want to save date of last run
Set rngSaveDate = Sheets("Sheet1").Range("K1")

'Edit "Sheet1" to your sheet name
With Sheets("Sheet1")
'Test if saved date is >= 7 days before dateToday.
If dateToday - rngSaveDate.Value >= 7 Then
.Range("A34").Cut
.Range("A1").Insert Shift:=xlDown

'Test if dateToday is a Sunday and
'if not set to previous Sunday date.
Do
If Weekday(dateToday, vbSunday) = 1 Then
rngSaveDate.Value = dateToday
Exit Do
Else
dateToday = dateToday - 1
End If
Loop
End If
End With
End Sub